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

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.
"mantrid" wrote:

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

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" wrote:

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

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

"Mike" <(E-Mail Removed)> wrote in message
news348023A-215C-4A10-AFA9-(E-Mail Removed)...
> 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.
> "mantrid" wrote:
>
> > 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
> >
> >
> >

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


"Art" <(E-Mail Removed)> wrote in message
news:0E93C8E3-044D-4862-8B4A-(E-Mail Removed)...
> 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" wrote:
>
> > 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
> >
> >
> >

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

"mantrid" <(E-Mail Removed)> wrote in message
news:3nNTh.33$(E-Mail Removed)...
> 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
>
> "Mike" <(E-Mail Removed)> wrote in message
> news348023A-215C-4A10-AFA9-(E-Mail Removed)...
> > 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.
> > "mantrid" wrote:
> >
> > > 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
> > >
> > >
> > >
>
>

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" wrote:

> Thanks
> Unfortunately your formula is throwing up the #VALUE! in each cell
> Any ideas?
> Ian
>
>
> "Art" <(E-Mail Removed)> wrote in message
> news:0E93C8E3-044D-4862-8B4A-(E-Mail Removed)...
> > 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" wrote:
> >
> > > 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
> > >
> > >
> > >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:81D9FF68-16B2-47F6-B614-(E-Mail Removed)...
> 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" wrote:
>
> > Thanks
> > Unfortunately your formula is throwing up the #VALUE! in each cell
> > Any ideas?
> > Ian
> >
> >
> > "Art" <(E-Mail Removed)> wrote in message
> > news:0E93C8E3-044D-4862-8B4A-(E-Mail Removed)...
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:81D9FF68-16B2-47F6-B614-(E-Mail Removed)...
> > 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" wrote:
> >
> > > Thanks
> > > Unfortunately your formula is throwing up the #VALUE! in each cell
> > > Any ideas?
> > > Ian
> > >
> > >
> > > "Art" <(E-Mail Removed)> wrote in message
> > > news:0E93C8E3-044D-4862-8B4A-(E-Mail Removed)...
> > > > 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" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Thank Art
Your a genius, that work great
Ian


"Art" <(E-Mail Removed)> wrote in message
news:6E654817-A5AE-4A2F-A41D-(E-Mail Removed)...
> 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" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:81D9FF68-16B2-47F6-B614-(E-Mail Removed)...
> > > 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" wrote:
> > >
> > > > Thanks
> > > > Unfortunately your formula is throwing up the #VALUE! in each cell
> > > > Any ideas?
> > > > Ian
> > > >
> > > >
> > > > "Art" <(E-Mail Removed)> wrote in message
> > > > news:0E93C8E3-044D-4862-8B4A-(E-Mail Removed)...
> > > > > 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" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >