Summing a column from a different starting point

**Lokii** · 08-25-2008, 01:18 PM

This will probably seem a bit confusing but here we go anyway.

I have a sheet that is populated each day. I want to present the sum of the column, but I want it to be based on the last column with data on it.
Every day new data is automatically entered into Column B. I need to sum the column but I need it from the previous day. From the example below I would need the sum of B1 thru B:4. I can do that but I want it to be mobile. I want it to automatically give me the sum of B1:b5 tomorrow.
I am doing this because I am tracking "today's" number and "yesterday's" number. I was trying to do it using vlookup and offset to set the starting point based on the date. But I am unsure how.
A B
1 8/20/08 9
2 8/21/08 20
3 8/22/08 16
4 8/23/08 9
5 8/24/08 11
6 8/25/08
7 8/26/08

**Ron Coderre** · 08-25-2008, 01:30 PM

Using your posted data in A1:B8

Try something like this:

Please Login or Register  to view this content.

If today is 25-Aug-2008, the formula sums B1:B4 and returns 54.

Is that something you can work with?
Post back if you have more questions.

**Lokii** · 08-25-2008, 01:54 PM

That will not quite do it. It needs to be variable and present the current data in one location.

Maybe this will help clarify in B11 I have the Sum of the whole column. In B12 I need the sum of the column less the previous day. So on the 24th the MTD prev cell would be the sum of B1 thru B2. And on the 25th the MTD prev would be the sum of B1 thru B3 and so on. What this will do is keep the number in one spot so that it can be pulled to a different sheet that shows yesterdays total and the day before as well. This sheet will be used in a presentation so it cant have the data from every day on it. What I was thinking was that I could find the last cell with data and then sum every cell above that one.

A B
1 8/22/2008 16
2 8/23/2008 9
3 8/24/2008 11
4 8/25/2008
5 8/26/2008
6 8/27/2008
7 8/28/2008
8 8/29/2008
9 8/30/2008
10 8/31/2008
11 MTD 300
12 MTD Prev ?

**Ron Coderre** · 08-25-2008, 02:03 PM

Try this...

Please Login or Register  to view this content.

Does that help?

**Lokii** · 08-25-2008, 02:33 PM

Originally Posted by Ron Coderre

Try this...

Please Login or Register  to view this content.

Does that help?

This still just gives me the sum of B1:B10 in total. I just want B1:until a specific cell determined by the last cell data was entered in. Meaning IF I had data in B1 thru B20 It would provide me the sum thru B19. But only if there was data in B20. And the next day it would automatically change to present the sum of 1 thru 20 in the same cell based on there being data in B21.

Does that make any sense?

**Ron Coderre** · 08-25-2008, 02:48 PM

Ok....I think I *finally* understand.

The numbers will be entered contiguously down the column.
You want to sum from the top to the last number
AND
from the top to the next-to-the-last number
...right?

Try this:

Please Login or Register  to view this content.

Am I closer this time?

**Lokii** · 08-25-2008, 03:11 PM

Yes! That is exactly what I am looking for. Now please explain exactly what the 10^99 means and does. (I need the theory behind it so I understand it and can apply it elsewhere in the future.

**Ron Coderre** · 08-25-2008, 03:31 PM

Excel has a few functions that try to find an item in a list.

The MATCH function is one of them.
It takes the form

Please Login or Register  to view this content.

It has two options for the 3rd argument
....Exact Match: 0
or
....Approximate Match: 1 (or omitted)

Exact Match does what it says...if no exact match...it returns an error.
Otherwise, it returns the sequential position of the matched item in the list.

Approximate Match, the one we're using here, assumes the items are
listed in order and returns the position of the largest item that is
less than or equal to the search item.

However....if the searched item is larger than all other items in the list
it returns the position of the LAST ITEM of that type (numeric vs text)
...even if the list is not sorted.

So...regarding this formula:

Please Login or Register  to view this content.

The MATCH function is trying to find 10 to the 99th power
(an impossibly large number for Excel to even contain).
Not finding it, the function returns the position of the
last numeric value in the list.

The INDEX section of the above formula returns the cell in the position
just before the last numeric cell in the list.

I hope that helps.
Post back with more questions.

Summing a column from a different starting point

LinkBack

Thread Tools

Rate This Thread

Display