Cumulative totals

[mantrid]

Can anyone help
I have a list of numbers with an associated date in two coulmns which are
NOT in date order. I wish to add up the numbers in date order creating a
cumulative total to be recorderd in a third cell on the line of the latest
number added.

looking something like

3/5/2003 4 9
2/2/2002 5 5
7/5/2007 1 21
8/9/2004 9 20
6/7/2003 2 11

I have used VBA and can write functions if this is the best approach
Thanks in advance

Ian

 

[Mike]

Try this Assuming that the category column is column A, the values column is
column B, and the headings are in row 1, the running category total formula
at the top row is:
=SUMIF(INDIRECT("A2:A"&ROW()),"="&A2,INDIRECT("B2:B"&ROW()))
Copy the formula for all rows used.

 

[Art]

Using the setup that Mike suggested with regard to rows and columns, try this
formula:

=SUMPRODUCT((A2>=A$2:A$6)*B$2:B$6)

 

[mantrid]

Hi
Thanks for that
Doesnt seem to work. That numbers are not being added up correctly. Would
you be able to explain what the formula is doing step by step and maybe I
can see why it isnt working for me
Thanks for your time
Ian

 

[mantrid]

Thanks
Unfortunately your formula is throwing up the #VALUE! in each cell
Any ideas?
Ian

 

[mantrid]

If it helps
I can see what your formula is doing to the numbers. It is only calculating
a cumulative total for dates that are the same e.g. on the 2/2/2002 below

3/5/2003 4 4
2/2/2002 5 5
2/2/2002 1 6
8/9/2004 9 9
6/7/2003 2 2

whereas I want a cumulative value displayed in each row by date order
(below)

3/5/2003 4 10 (third number to add)
2/2/2002 5 5 (first number to use)
2/2/2002 1 6 (second number to add)
8/9/2004 9 21 (fifth number to add)
6/7/2003 2 12 (fourth number to add)

Thank you
Ian

 

[Art]

Could you check the formula and make sure there are semi-colons and not
commas, etc.? Also make sure that the numbers in column B are all numbers.
I've tried breaking the formula and those seem to be the only things that
cause #Value to appear. If you can't spot anything, try reducing the range
you're using -- if it starts to work, there might be a problem in the part of
the range you eliminated. Also, if you reduce the range small enough, try
re-typing the values in.

 

[mantrid]

OK
I got it to work with some simple values. I just got to see whats stoping it
working with my actual data.

However, in the meantime while testing with some dates i found dates that
are the same, display the cumulative total of the last one and not each one
seperately eg. i get


23/04/2002 1 2
03/08/2003 1 4
03/08/2003 1 4
01/01/2001 1 1
04/06/2006 1 6
02/07/2005 1 5

instead of

23/04/2002 1 2
03/08/2003 1 3
03/08/2003 1 4
01/01/2001 1 1
04/06/2006 1 6
02/07/2005 1 5

Any ideas?
Also so I understand for future could you explain what the formula is doing?

Again thanks for your help.

Ian

 

[Art]

Okay...

Try this for your formula:

=SUMPRODUCT((A2>=A$2:A$8)*B$2:B$8)-SUMPRODUCT((ROW(A2)<ROW(A$2:A$8))*(A2=A$2:A$8)*B$2:B$8)

I know it's getting kind of long.

The way this works is that SUMPRODUCT evaluates each of those expressions in
parenthesis as a logical expression. The original formula will be easier to
understand:

=SUMPRODUCT((A2>=A$2:A$6)*B$2:B$6)

It checks A2 against each of the values in A2:A6 one by one. This results
in an array of 1's and 0's. These get mulitplied item by item against B2:B6
resulting in a sum of B's when A2 is greater than the corresponding value in
the A column.

You might want to look at:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new

 

[mantrid]

Thank Art
Your a genius, that work great
Ian

Okay...

Try this for your formula:

=SUMPRODUCT((A2>=A$2:A$8)*B$2:B$8)-SUMPRODUCT((ROW(A2)<ROW(A$2:A$8))*(A2=A$2
:A$8)*B$2:B$8)

I know it's getting kind of long.

The way this works is that SUMPRODUCT evaluates each of those expressions in
parenthesis as a logical expression. The original formula will be easier to
understand:

=SUMPRODUCT((A2>=A$2:A$6)*B$2:B$6)

It checks A2 against each of the values in A2:A6 one by one. This results
in an array of 1's and 0's. These get mulitplied item by item against B2:B6
resulting in a sum of B's when A2 is greater than the corresponding value in
the A column.

You might want to look at:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new

 

[mantrid]

By the way, the reason it didnt work with my data initially is the the colum
of numbers were generated with a formula and it didnt like it when the
formula returned a blank space. To get round it I used an IF statement to
place a 0 in the field instead and then used a conditional format to hide
the 0 by making the same colour as the cell. Not very elegant i know but I
dont use excel much

Ian