Running Total Formula

**DonkeyOte** · 09-24-2011, 02:47 AM

Based on the example:

First: don't use merged cells... in the case of D3, remove the merge area, highlight D3:E3 and use Centre Across Selection option.

Second:

D3:
=SUM(C4:F15)+IF(C43<6,SUMPRODUCT(((MOD(B16:B27-1,6)+1)>(MOD(INDEX(B4:B15,MATCH(TRUE,INDEX(ISNUMBER(C4:C15),0),0))-1,6)+1))*C16:E27))

The above could be applied to each of your examples.

**Killer17** · 09-24-2011, 05:17 PM

Hey DonkeyOte

The formula works correctly until example 7 it calculates a total of 8914 when it should be 4016

The first 6 examples the formula works great.

Thanks

**DonkeyOte** · 09-25-2011, 01:51 AM

@Killer17, no need to quote prior posts in their entirety in your replies (only relevant parts)

Originally Posted by Killer17

The formula works correctly until example 7 it calculates a total of 8914 when it should be 4016

Yes, it dawned on me whilst out yesterday afternoon that I had left a legacy reference in the formula (C43) which would generate the error you outline.

On further reflection, if we can assume:

1. that where a # (Col B etc) is "active" a balance will always exist in the first column
2. that the # (Col B etc) will always be 1:12

then we can simplify slightly

D3:
=SUM(C4:F15)+IF(COUNT(C4:C15)<6,SUMPRODUCT(((MOD(B16:B27-1,6)+1)>(MOD(COUNT(C4:C15)-1,6)+1))*C16:E27))

**Killer17** · 09-25-2011, 12:26 PM

=SUM(C4:F15)+IF(COUNT(C4:C15)<6,SUMPRODUCT(((MOD(B16:B27-1,6)+1)>(MOD(COUNT(C4:C15)-1,6)+1))*C16:E27))

Hey DonkeyOte thanks for the help that works perfect.

I do have other question if I wanted to extend the Col B cells from 1:12 to 1:34 how would I go about doing this?

Thanks again!

**Killer17** · 09-25-2011, 08:12 PM

Originally Posted by Killer17

I do have other question if I wanted to extend the Col B cells from 1:12 to 1:34 how would I go about doing this?

I figured it out thanks again for your help

**Killer17** · 09-25-2011, 11:08 PM

The formula works great on the test sheet but when I transfer it to my real page it’s not calculating correctly. I know the reason why

Here's the formula I have adjusted.

=SUM(G27:I60)+IF(COUNT(G27:G60)<17,SUMPRODUCT(((MOD(D61:D94-1,17)+1)>(MOD(COUNT(G27:G60)-1,17)+1))*G61:I94))

In Cell G27:I60 I have formula and its returning a result of 0 because no data has been entered.

With your formula is reading it as data and it’s doing the calculation incorrectly because of the zero’s in cells G27:I60

Thanks

**DonkeyOte** · 09-26-2011, 07:22 AM

If 0 is never a valid result then you might change the pre-emptive COUNT test to a COUNTIF test

=SUM(G27:I60)+IF(COUNTIF(G27:G60,"<>0")<17,SUMPRODUCT(...etc...))

The above assumes G27:G60 will only ever contain numerics.

If the above does not reflect reality I would suggest posting a representative sample file.