Home

COUNTIFS function

hshah62
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
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.

@Jan Karel Pieterse 

Can you show me the way to do this?

@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.

@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.

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies