Running Total Formula

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