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. |
All times are GMT +1. The time now is 08:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com