Running Total Formula

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

**Killer17** · 09-26-2011, 08:19 PM

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

Ok Made the changes to the formula

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

and it's still not adding up correctly.

I have added an example sheet

Thanks

**DonkeyOte** · 09-27-2011, 04:10 AM

I believe the correct total should be 14367 (exclude 1 & 18 from bottom data set given existence of 1 in upper data set).

Irrespective of the above the existing formula still fails to calculate correctly and this is related to an earlier point where the implication was that the upper data set contained zeroes which should be excluded when determining set to calculate... hence my point below re: revision to COUNTIF:

Originally Posted by D.O

The above assumes G27:G60 will only ever contain numerics

In your latest sample the above is not reality ergo the COUNTIF generates an incorrect value... if you change <>0 to >0 then you will find you generate 14367. At this stage I think you need to be fairly clear in terms of requirements.

I would also add that given the multiple of Arrays in your lower dataset this model will perform very poorly in terms of calculation time.

**Killer17** · 09-27-2011, 07:56 PM

Sorry DonkeyOte

I don't understand the

The above assumes G27:G60 will only ever contain numerics

.

Your right with the the changes to the formula it's adding correctly now.

I have one final question, what if I wanted to exclude 1 & 34 instead of 1 &18?

Exclude Examples

2 & 33
3 & 32
4 & 31
5 & 30

Thanks