Count cells in a row that are "color" conditionally formatted by column

Count cells in a row that are "color" conditionally formatted by column.


I need to count all the cells in a row that are automatically highlighted by conditional formatting (which is done by column).

In the attached file, in cell Q3, it should say 2 (since there are only two conditionally formatted cells for Group 1). In cell T3, it should show 2 also since only two cells have been conditionally highlighted for that group 2. In the next row 4, group one should show 2 and group two should show zero. In row 5, "Group 1" should show 3 and "Group 2" should show 2. Etc. etc.

This repeats per row.

I have tried with the obvious VBA but I can't make it work. All I get is #VALUE!

Thanks so much in advance.

Ps. I searched through the forum, here and elsewhere, but I was not able to find a working solution for this particular scenario. I found some similar, but not quite the same and I couldn't adapt their solutions.

Can you please show me how? That's why I'm here, because I don't know how to do so. Can you do it with the attached file so I can see a working solution?

Thanks.

Thanks for replying.

I would prefer a formula. However, if you can also teach me how to do it with VBA? I already tried with VBA to no avail. That's what the attached file has. Again, I think a formula is the easiest way to manipulate it later on.

Thanks again.

Thank you JeteMc.

It worked exactly as needed.

I do have some questions.

One: I tried to use the same formula (using it in cell U3) for "Group 2" which goes from column I through L but it only works if I add a -1 at the end of the formula.

In other words, THIS WORKS:
=SUM(SUMPRODUCT(--(MAX(I$3:I$12)=I3)),SUMPRODUCT(--(MAX(J$3:J$12)=J3)),SUMPRODUCT(--(MAX(K$3:K$12)=K3)),SUMPRODUCT(--(MAX(L$3:L$12)=L3))-1)

But this does not (and this is based on the original formula you gave me), so the question is why not? - I simply want to understand why not since it's the same logic/procedure as the first group.
=SUM(SUMPRODUCT(--(MAX(I$3:I$12)=I3)),SUMPRODUCT(--(MAX(J$3:J$12)=J3)),SUMPRODUCT(--(MAX(K$3:K$12)=K3)),SUMPRODUCT(--(MAX(L$3:L$12)=L3)))


Second: How do I go about "adding" the values of those highlighted cells independently of the first formula which counts them? Since the formula to count them is using "Sum" I got stopped on my tracks when thinking how to adapt your formula to add. I need this "adding" to start in cells R3 (and below) and V3 (and below).

Please see attached file. Note: the "Count" formulas are working. I only provide this attachment to illustrate the "adding" values needed in columns R and V.

Thank you so much.

Thank you so much.

I understand now.

The second formula worked as needed too.

Mark solved: Done.

You too have a blessed day and be safe.