COUNTIF specific text on visible cells

[eugchen]

i have a list of 164 stocks on the sheet. each has an associated sector to it. if i include all 164, i want to create a simple pie chart showing the breakdown. so total data on Column A and B, column D and E show the breakdown. im sure COUNTIF formula in column E.
easy enough.

now, lets say i want to filter some out in column B, lets say we want to remove all communications sector, or hide them. how do i get column E to reflect that and show 0, instead of 3?

thanks

[Glenn Kennedy]

=SUMPRODUCT(($B$3:$B$200=D7)*(SUBTOTAL(103,OFFSET($B$3,ROW($B$3:$B$200)-MIN(ROW($B$3:$B$200)),0))))

will countif visible cells. If you then use the data in the chart (currently it is using externally held data which I cannot access, then turn the 0 into a #N/A error.
=IFERROR(1/(1/SUMPRODUCT(($B$3:$B$200=D7)*(SUBTOTAL(103,OFFSET($B$3,ROW($B$3:$B$200)-MIN(ROW($B$3:$B$200)),0))))),NA())

Excel will ignore, and not plot, the NA values.


Then use this:
=SUBTOTAL(3,$B$3:$B$200)

to return the total numbe rof visible rows.