Running Total Formula

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

@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))

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

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.