I'm pulling my hair out trying to figure out the best formula to use for this task. I've goolged & came up with this formula:

=IF(COUNTA(J2:J8="Yes"),"Yes","")

when J2, et al, contain the formula:

=IF(E5=$J$1,"Yes","")

however, when I have a range that does not return any "yes" values, my counta formula still returns a "yes". what am I doing wrong? is this not the best formula for this task?

in addition, this is a sub-totaled spreadsheet as opposed to a pivot (just seemed to work better w/all my formatting needs). what's the best way to CORRECTLY copy down the formulas on the sub-total line

*other than Go To Special > Visible Only?*the formulas aren't lining up correctly (overlapping w/above formula/ranges).

THANKS so much!!!