SOLVED

Count number of cells in each row assigned different colors through Conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2316464%22%20slang%3D%22en-US%22%3ECount%20number%20of%20cells%20in%20each%20row%20assigned%20different%20colors%20through%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2316464%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20community!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20new%20here%20and%20have%20a%20question%20on%20how%20to%20count%20number%20of%20cells%20in%20each%20row%20that%20have%20been%20assigned%20different%20colors%20through%20Conditional%20formatting%20in%20Excel%20365.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20done%20extensive%20searches%20and%20replies%20to%20a%20post%20from%202017%20by%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3Bseems%20to%20be%20the%20closest%20to%20what%20I%20need%20but%20I%20couldn't%20quite%20get%20it%20to%20replicate%20to%20my%20situation.%20I%20replied%20to%20the%20post%20but%20it%20appears%20that%20it's%20been%20since%20deleted%2C%20perhaps%20due%20to%20age%20or%20completion%2C%20therefore%20I'm%20reposting%20the%20same%20inquiry%20afresh.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20sample%20file%3A%20sampleCFcolorCount.xlsm%20as%20a%20reference%2C%20or%20for%20assistance%20in%20accomplishing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EThe%20background%20colors%20on%20cells%20%24D%243%3A%24F%245%2C%20a%20named%20range%20called%20COLORS2%2C%20have%20been%20applied%20using%20Conditional%20Formatting%20based%20on%20criteria%20met%20using%20values%20in%20columns%20B%20and%20C.%3C%2FLI%3E%3CLI%3EA%20value%20of%209%20on%2029-Apr%20is%20assigned%20a%20background%20color%20of%20%22Green%22%20(Hex%20%2392D050)%20because%20it%20fell%20between%20the%20range%20of%208.5%20and%2010.5%20per%20rules%20of%20item%20A.%3C%2FLI%3E%3CLI%3EThe%20other%20values%20below%20and%20above%20that%20range%20were%20assigned%20%22Red%22%20(Hex%20%23FF0000).%3C%2FLI%3E%3CLI%3EIf%20either%20values%20equaled%20the%20range%20boundaries%208.5%20or%2010.5%2C%20they%20would%20be%20assigned%20%22Orange%22%20(Hex%20%23FFC000)%2C%20while%20blanks%20would%20not%20be%20assigned%20a%20background%20color%20and%20therefore%20should%20not%20be%20counted.%3C%2FLI%3E%3CLI%3EThe%20CF%20formulas%20assigned%20are%20as%20follows%2C%20and%20apply%20to%26nbsp%3B%24D%243%3A%24F%245%3CUL%20class%3D%22lia-list-style-type-circle%22%3E%3CLI%3EBlank%20ISBLANK(D8)%3C%2FLI%3E%3CLI%3EGreen%20AND(D8%26gt%3B%24B8%2CD8%26lt%3B%24C8)%3C%2FLI%3E%3CLI%3EOrange%20OR(D8%3D%24B8%2CD8%3D%24C8)%3C%2FLI%3E%3CLI%3ERed%20AND(NOT(ISBLANK(D8))%2COR(D8%26lt%3B%24B8%2CD8%26gt%3B%24C8))%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20could%20not%20find%20a%20clean%20way%20to%20arrive%20at%20a%20COUNT%20for%20the%20expected%20values%20for%20each%20row%20(not%20aggregated)%20based%20on%20the%20conditions%20described%20(see%20attached%20sample).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20this%20be%20accomplished%20without%20VB%20scripts%3F%20Whether%20via%20scripts%20or%20without%2C%20kindly%20provide%20me%20with%20a%20step-by-step%20explanation%20(I'm%20not%20an%20Excel%20expert)%20on%20how%20to%20accomplish%20this%20request.%20Please%20also%20feel%20free%20to%20modify%20the%20sample%20file%20in%20your%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks!%3C%2FP%3E%3CP%3E-SMM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2316464%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2317002%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20number%20of%20cells%20in%20each%20row%20assigned%20different%20colors%20through%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317002%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042471%22%20target%3D%22_blank%22%3E%40SteveMM%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERefer%20to%20the%20conditions%20instead%20of%20to%20the%20colors%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20H3%3A%26nbsp%3B%20%3DCOUNTIFS(%24D3%3A%24F3%2C%22%26gt%3B%22%26amp%3B%24B3%2C%24D3%3A%24F3%2C%22%26lt%3B%22%26amp%3B%24C3)%3C%2FP%3E%0A%3CP%3EIn%20I3%3A%26nbsp%3B%26nbsp%3B%20%3DCOUNTIF(%24D3%3A%24F3%2C%24B3)%2BCOUNTIF(%24D3%3A%24F3%2C%24C3)%3C%2FP%3E%0A%3CP%3EIn%20J3%3A%26nbsp%3B%20%3DCOUNTIFS(%24D3%3A%24F3%2C%22%26lt%3B%22%26amp%3B%24B3%2C%24D3%3A%24F3%2C%22%26lt%3B%26gt%3B%22)%2BCOUNTIFS(%24D3%3A%24F3%2C%22%26gt%3B%22%26amp%3B%24C3%2C%24D3%3A%24F3%2C%22%26lt%3B%26gt%3B%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2317068%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20number%20of%20cells%20in%20each%20row%20assigned%20different%20colors%20through%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317068%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BGreat%20way%20to%20think%20about%20it%20-%20referencing%20the%20conditions%20instead%20of%20the%20actual%20colors%20-%20and%20it%20works%20beautifully!%20I%20would%20never%20have%20come%20up%20with%20those%20countifs%20formulas%3B%20thank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello community!

 

I'm new here and have a question on how to count number of cells in each row that have been assigned different colors through Conditional formatting in Excel 365.

 

I have done extensive searches and replies to a post from 2017 by @Jamil Mohammad seems to be the closest to what I need but I couldn't quite get it to replicate to my situation. I replied to the post but it appears that it's been since deleted, perhaps due to age or completion, therefore I'm reposting the same inquiry afresh.

 

See attached sample file: sampleCFcolorCount.xlsm as a reference, or for assistance in accomplishing.

 

  • The background colors on cells $D$3:$F$5, a named range called COLORS2, have been applied using Conditional Formatting based on criteria met using values in columns B and C.
  • A value of 9 on 29-Apr is assigned a background color of "Green" (Hex #92D050) because it fell between the range of 8.5 and 10.5 per rules of item A.
  • The other values below and above that range were assigned "Red" (Hex #FF0000).
  • If either values equaled the range boundaries 8.5 or 10.5, they would be assigned "Orange" (Hex #FFC000), while blanks would not be assigned a background color and therefore should not be counted.
  • The CF formulas assigned are as follows, and apply to $D$3:$F$5
    • Blank ISBLANK(D8)
    • Green AND(D8>$B8,D8<$C8)
    • Orange OR(D8=$B8,D8=$C8)
    • Red AND(NOT(ISBLANK(D8)),OR(D8<$B8,D8>$C8))

I could not find a clean way to arrive at a COUNT for the expected values for each row (not aggregated) based on the conditions described (see attached sample).

 

Can this be accomplished without VB scripts? Whether via scripts or without, kindly provide me with a step-by-step explanation (I'm not an Excel expert) on how to accomplish this request. Please also feel free to modify the sample file in your response.

 

Many thanks!

-SMM

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@SteveMM 

Refer to the conditions instead of to the colors:

 

In H3:  =COUNTIFS($D3:$F3,">"&$B3,$D3:$F3,"<"&$C3)

In I3:   =COUNTIF($D3:$F3,$B3)+COUNTIF($D3:$F3,$C3)

In J3:  =COUNTIFS($D3:$F3,"<"&$B3,$D3:$F3,"<>")+COUNTIFS($D3:$F3,">"&$C3,$D3:$F3,"<>")

 

Fill down.

@Hans Vogelaar Great way to think about it - referencing the conditions instead of the actual colors - and it works beautifully! I would never have come up with those countifs formulas; thank you so much!