Home

COUNTIFS function

%3CLINGO-SUB%20id%3D%22lingo-sub-771804%22%20slang%3D%22en-US%22%3ECOUNTIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771804%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20attached%20file%2C%20I%20need%20Count%20(in%20col.%20M)%20of%20all%20the%20cells%20in%20a%20row%20(range%20B2%3A%20L2)%20that%20are%20highlighted%20in%20pink%2C%20how%20do%20I%20do%20that%20using%20some%20excel%20function%3C%2FP%3E%3CP%3EFile%20is%20attached%20here%20for%20reference%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-771804%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-771819%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771819%22%20slang%3D%22en-US%22%3EExcel%20does%20not%20make%20is%20easy%20for%20you%20to%20do%20math%20based%20on%20formatting.%20You'll%20have%20to%20duplicate%20the%20conditions%20in%20the%20conditional%20formatting%20rules%20into%20a%20formula%20to%20derive%20the%20count.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772081%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772081%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20show%20me%20the%20way%20to%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772329%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772329%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381521%22%20target%3D%22_blank%22%3E%40hshah62%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20happened%20to%20see%20your%20post%20and%20made%20a%20search%20on%20the%20topic%20remembering%20that%20I%20have%20seen%20(but%20not%20used)%20cell%20coloring%20logic%20somewhere%20before.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20this.%20Maybe%20this%20gives%20you%20something%20to%20dwell%20upon%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2013%2F12%2F12%2Fcount-sum-by-color-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2013%2F12%2F12%2Fcount-sum-by-color-excel%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMind%20you%20I%20know%20nothing%20more%20of%20the%20subject%20or%20ablebits.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772374%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772374%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381521%22%20target%3D%22_blank%22%3E%40hshah62%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20case%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(--(%24B2%3A%24H2%26gt%3B%7B0.06%2C8%2C6%2C10%2C3%2C0.5%2C0.5%7D))%2BSUMPRODUCT(--(%24J2%3A%24L2%26gt%3B%7B10%2C1%2C1%7D))%2B(%24I2%26lt%3B100000000000)%3C%2FPRE%3E%0A%3CP%3Ejust%20use%20logic%20of%20your%20conditional%20formatting%20rules.%20Please%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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
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.

@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
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies