Counting visible cells by conditional formatting fill

[pliskers]

I have a table with conditional formatting of a few different fill colors. If a slicer is applied that hides some of the rows, is there a way to count the number of visible cells in a column for each of the fill colors used, without having to apply a color filter manually?

Stumped!

 

[JamesCanale]

If it's conditional formatting, there are some rules you can repeat.

Here is a table with a slicer (I can't put the slicer on xl2bb - but it' works on the "Rate" column. You put in the hider column with the subtotal field that only counts visible rows.

MrExcelPlayground11.xlsx
	A	B	C	D
1	Person	Rate	hider	Weight
2	John	10	1	250
3	Fred	20	1	150
4	Harry	10	1	200
5	Jamie	15	1	175
6	Wilson	20	1	150
7	Susie	25	1	110
8	Mary	10	1	150
9	Joe	25	1	190
10
11
12	All	8
13	Red	1
14	Yellow	3
15	Green	4
Sheet4

Cell Formulas
Range	Formula
C2:C9	C2	=SUBTOTAL(102,B2)
B12	B12	=SUM(Table1[hider])
B13	B13	=SUMIFS(Table1[hider],Table1[Weight],">=210")
B14	B14	=SUMIFS(Table1[hider],Table1[Weight],"<210",Table1[Weight],">=170")
B15	B15	=SUMIFS(Table1[hider],Table1[Weight],"<170")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D9	Expression	=$D2>=210	text	NO
A2:D9	Expression	=AND($D2>=170,$D2<210)	text	NO
A2:D9	Expression	=$D2<170	text	NO

 

[pliskers]

That’s a clever solution, but unfortunately there are about 100 conditionally formatted columns, so it would mean doubling the column count to accommodate. I was hoping for a single formula at the top of each column that woukd reference a single cell with the formatting I want to count.

Thanks for the suggestion.