May 03 2021 01:40 AM
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.
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
May 03 2021 04:26 AM
SolutionRefer 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.
May 03 2021 04:59 AM - edited May 03 2021 06:25 AM
@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!
May 03 2021 04:26 AM
SolutionRefer 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.