Can you count shaded cells? 2

[wec43wec]

Are you able to create a formula that would count the number of cells within a row or columns that are shaded?

I have a data base that I have shaded cells that meet a certain criteria.

Example:
A1 thru A10 contains data, however all even number rows are shaded red. I need to put a formula that will give me 5 as a result.

 

[AnotherHiggins]

Whatever logic you are using to determine which cells are shaded, use that logic in a formula.

Are you using Conditional Formatting to shade the cells? If not, I'd suggest you do.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

Building on what John has stated so well...

"A1 thru A10 contains data, however all even number rows are shaded red. I need to put a formula that will give me 5 as a result."
[tt]
Odd row count: =INT((COUNTA(A:A)+1)/2)
Even row count: =INT(COUNTA(A:A)/2)
[/tt]
assuming that a value is in column A for every shaded row.


Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[wec43wec]

It appears that I may have mislead your way of thinking in you giving me a suggestion to may problem.

The cells shaded between A1:A10 (even) rows was just an example. The rows are "not" shaded even/odd cells, rather they are shaded by the user without using the conditional formatting function.

In rows A1:A10, the user will mannually color cells based on certain conditions.

Example: Within the 10 cells (A1:A10) a value is only entered for any one of the 10 companies that has an active record. Afterwards the user will shade the cell if a notice has also been revceived from the company that has the active data.

Because of the second step, I do not see how I can use the conditional formatting with the two sets of data that I am collecting.

Thanks for any suggestions.

 

[AnotherHiggins]

Add a column where the user adds an X or checks a checkbox if the notice has been sent. Then you CAN use conditional formatting.

You can count shaded cells, but it would require VBA. But rather than go that route, I'd strongly urge you to just set up a good worksheet that will allow you to easily accomplish your goals.

Setting up the worksheet properly in the first place has several advantages. It will be easier to report on, the file size will be smaller and, if it grows big enough, it will run faster.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Hasit]

John has pretty much given you the answer. For some worked examples, see

http://www.cpearson.com/excel/colors.htm

 

[Blorf]

Neat link Hasit. I dunno if I will need it but still neat.

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[wec43wec]

Thanks ! I will use your suggestion.

Hasit - Great lnk !