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!
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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:
Top