Thread
:
Subtotal and IF Formula
View Single Post
#
2
Posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
Posts: 10,124
Subtotal and IF Formula
Since your title question refers to subtotal have you looked in the help
index for SUBTOTAL.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"MP" wrote in message
...
=IF(J2=J1,K1+1,1)
The above formula verifies if the value in J2 equals J1, if true, K2
equals
K1 +1, If false the count is returned to 1.
I dragged the formula from K2:100
Sheet1
Column J Column K
Run 1
Jump 1 **J3 does not equal J2, therefore the count is reset to
1
Jump 2 **J4 equals J3, therefore the total is (K3+1)
Run 1
Run 2
Run 3
Run 4 **J8 equals J7, therefore the total is (K7+1)
Jump 1 **J3 does not equal J2, therefore the count is reset to 7
Jump 2 **J3 does not equal J2, therefore the count is reset to 8
Spreadsheet Formulas
Cell Formula
K2 =IF(J2=J1,K1+1,1)
K3 =IF(J3=J2,K2+1,1)
K4 =IF(J4=J3,K3+1,1)
K5 =IF(J5=J4,K4+1,1)
K6 =IF(J6=J5,K5+1,1)
K7 =IF(J7=J6,K6+1,1)
K8 =IF(J8=J7,K7+1,1)
The formula needs to be adjusted to recalculate for visible cells only
when
columns are filtered using auto filter.
For instance say Column A (not shown) is filtered by a certain name
therefore, rows 6 & 7 are hidden. The total in K8 should read 2 in lieu of
4,
based on the fact that the only match above row 8 is row 5 (row 6 and row
7
are hidden)
Hope this helps.
Any help is appreciated.
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett