Subtotal and IF Formula

[MP]

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

 

[Don Guillett]

Since your title question refers to subtotal have you looked in the help
index for SUBTOTAL.