COUNTIFS function

Highlighted
New Contributor

In the attached file, I need Count (in col. M) of all the cells in a row (range B2: L2) that are highlighted in pink, how do I do that using some excel function

File is attached here for reference

 

4 Replies
Highlighted
Excel does not make is easy for you to do math based on formatting. You'll have to duplicate the conditions in the conditional formatting rules into a formula to derive the count.
Highlighted

@Jan Karel Pieterse 

Can you show me the way to do this?

Highlighted

@hshah62 

 

I happened to see your post and made a search on the topic remembering that I have seen (but not used) cell coloring logic somewhere before.

 

I found this. Maybe this gives you something to dwell upon:

https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/

 

Mind you I know nothing more of the subject or ablebits.

Highlighted

@hshah62 

In your case that could be

=SUMPRODUCT(--($B2:$H2>{0.06,8,6,10,3,0.5,0.5}))+SUMPRODUCT(--($J2:$L2>{10,1,1}))+($I2<100000000000)

just use logic of your conditional formatting rules. Please see attached.