Running Total Formula

**Sofistikat** · 09-23-2011, 12:58 AM

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** · 09-24-2011, 01:12 AM

Originally Posted by 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.

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

Running Total Formula

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Unique Running Total Formula Help!

Re: Unique Running Total Formula Help!

Re: Unique Running Total Formula Help!

Re: Unique Running Total Formula Help!

Re: Running Total Formula

Re: Running Total Formula

Re: Running Total Formula

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions