Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF with OR Function | Excel Worksheet Functions | |||
Subtotal function for hidden row | Excel Worksheet Functions | |||
Several function questions (nested functions) | Excel Worksheet Functions | |||
Nested if, sum & vlookup Function | Excel Worksheet Functions | |||
Why is the subtotal function in excel 2003 very slow compared to . | Excel Worksheet Functions |