Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal and IF Formula
=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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal Formula | Excel Worksheet Functions | |||
Subtotal Formula | Excel Worksheet Functions | |||
Formula Help - If and Subtotal(???) | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |