Quick question: Conditional counting and then some...

pdorion

New member
Joined
Mar 21, 2012
Messages
1
Reaction score
0
Points
0
Hey guys!

This is what i have at the moment, (its a mailing list)

CELL B5 =COUNTIF(*****!U2:U101,"YES")
CELL C5 =COUNTIF(*****!U2:U101,"NO")
CELL D5 =COUNTIF(*****!U2:U101,"RETURNED")

What I want to do is every time we color the cell a certain color like yellow, it will not count that cell.

Every month we submit a separate sheet to accounting and this should keep it updated without the hassle of manually counting. But every month we only count only the un-highlighted ones.

So what can I do to have it not count yellow highlighted cells???

Thanks!
 
Counting colours involves VBA.

You can instead use an in-cell trigger, example an "x" or checkmark, etc in a cell within the same row... then you can use conditional formatting to highlight the row based on that trigger being present... then you can use COUNTIFS (if you are in XL2007 or later) or SUMPRODUCT (for any version).

e.g.

=COUNTIFS(Sheet1!U2:U101,"YES",Sheet1!V2:V101,"x")

or

=SUMPRODUCT((Sheet1!U2:U101="YES")*(Sheet1!V2:V101="x"))

where column V contains your trigger.
 
Last edited:
Back
Top