Running Total Formula

[Killer17]

Hey all

Need some help in creating a unique running total formula.

I have created an example page for your viewing.

One week data entered two weeks a data not counted until all data from cell B16:G27 has been removed.

Thanks

[Sofistikat]

First question: Whats the difference between numbers in cells with yellow background vs numbers in cells with blue backgrounds?

Second question (which might be answered by the answer to the first question): Are the numbers in cells with yellow backgrounds dynamic (ie. determined by a formula) or static (ie. manually entered and unchanging)?

[Killer17]

First question: Whats the difference between numbers in cells with yellow background vs numbers in cells with blue backgrounds?)

The yellow numbers not to be counted verse the blue cells amounts need to be counted.
Example 1 New data entered in cell C15:E15

Count everything in blue cells, anything yellow cells don’t count C21:E21 & C37:E27
The remaining example show how it recounts week after week.

Second question (which might be answered by the answer to the first question): Are the numbers in cells with yellow backgrounds dynamic (ie. determined by a formula) or static (ie. manually entered and unchanging)?

all Cells from C4:E15 numbers are dynamic determined by a formula.

[Sofistikat]

I understand that only the blue cells are to be counted (summed). My question was intended to find out more about the nature of the yellow cells. In your first example, there are 2 rows of yellow cells, in the second, there are 4 rows, the third has 6 etc, so on and so forth.

What I need to know are the factors or formulas that determine how many rows of yellow cells there will be, which then determines the cells to be excluded from the sum.

I hope I'm being clear here.

[Killer17]

I understand that only the blue cells are to be counted (summed). My question was intended to find out more about the nature of the yellow cells. In your first example, there are 2 rows of yellow cells, in the second, there are 4 rows, the third has 6 etc, so on and so forth.

What I need to know are the factors or formulas that determine how many rows of yellow cells there will be, which then determines the cells to be excluded from the sum.

I hope I'm being clear here.

Whenever I add new data I need two cells of the old data to not be counted. This should continue until all old data isn’t being counted anymore from cell C16:E27

1st time new data is entered I need the formula to stop counting two cells one from cell C27:E27 & C21:E21

The 2nd time new data is entered I need the formula to stop counting C26:E26 & C20:E20 and so on until all old data has been eliminated and its only counting current data.

[DonkeyOte]

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]

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]

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

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

[arthurbr]

Killer , as DO already asked, DO NOT QUOTE ENITIRE POSTS UNNECESSARILY. Thank you !

[Killer17]

Killer , as DO already asked, DO NOT QUOTE ENITIRE POSTS UNNECESSARILY. Thank you !

Sorry my bad