Nested --- Subtotal IF function
 

How do I do this?

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

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

Nested --- Subtotal IF function
 

=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

--
Greetings from New Zealand
Bill K
"Lisa Beach" wrote in message
...
How do I do this?

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

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

Nested --- Subtotal IF function
 

=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)


"Lisa Beach" wrote in message
...
How do I do this?

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

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

Nested --- Subtotal IF function
 

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)


"Lisa Beach" wrote in message
...
How do I do this?

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

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