Nested --- Subtotal IF function

[Lisa Beach]

How do I do this?

I'm using autofilter to hide rows that = 0.
to get the total I have
Subtotal (9, D250)

Now I need to count if column A = A1
COUNTIF(A2:A50, A1)

The only problem w/ the above is that it counts the hidden rows. How do I
count only the visible rows?

Thanks in advance.

Lisa

 

[Bill Kuunders]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A50,ROW(A2:A50)
-MIN(ROW(A2:A50)),,1)), --(A2:A50=A1))
Got this from
http://www.contextures.com/xlFunctions04.html#Top

 

[Bob Phillips]

=SUMPRODUCT(($A$2:$A$50=A1)*(SUBTOTAL(9,OFFSET($D$1,ROW($D$2:$D$50)-ROWS($D$
1),,1))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Sorry, to count use

=SUMPRODUCT(($A$2:$A$50=A1)*(SUBTOTAL(3,OFFSET($D$1,ROW($D$2:$D$50)-ROWS($D$
1),,1))))

--

HTH

RP
(remove nothere from the email address if mailing direct)