Cumulative Total

[Guest]

I have data as follows: I would like to create a formula that gives me a
cumulative balance by owner by date -- How do I do that? Thanks.
Owner Date Amount
1 01/01/90 100.00
1 02/01/90 -50.00
1 03/01/90 -25.00
2 01/01/90 50.00
2 02/01/90 75.00

 

[JulieD]

Hi

not sure what you mean by owner by date (as all your dates for the owners
are different), however here's a couple of ideas:

=SUMIF(A2:A100,1,C2:C100)
will give you the total of amounts for owner 1

=SUMPRODUCT(--(A2:A100=1),--(B2:B100=DATEVALUE("1/1/05")),C2:C100)
will give you the total of amounts for owner 1 for the 1/1/05

or look at using pivot tables to summaries your data in many different ways
- check out www.contextures.com/tiptech.html for details on how to use pivot
tables.

Cheers
JulieD

 

[JE McGimpsey]

One way:

D2: =SUMIF($A$2:A2,A2,$C$2:C2)

Copy down as far as necessary.

 

[Guest]

Thankyou, I'm sorry I should have put in my question that I am looking for a
formula that gives me a running cumulative by owner by saledate total. Is
there a way to do that with the sumif?

 

[Guest]

Thanks, I'm looking for the running cumulative total by owner by saledate.
so for any given sale date I would like to calculate the cumulative total up
to that point in time. Any additional Ideas. Thanks.

 

[JE McGimpsey]

If your data is sorted by date, that's what the formula I gave you
does...

If they're not sorted by date, a Pivot Table would probably be your best
choice.

 

[JulieD]

Hi
understand now ...

try
=SUMPRODUCT(--(A2:A6=F2),--(B2:B6<=E2),C2:C6)

where cell F2 contains the owner and E2 contains the date.

Cheers
JulieD

 

[Guest]

perfect, thanks.

Hi
understand now ...

try
=SUMPRODUCT(--(A2:A6=F2),--(B2:B6<=E2),C2:C6)

where cell F2 contains the owner and E2 contains the date.

Cheers
JulieD

 

[Guest]

Perfect, thanks.

If your data is sorted by date, that's what the formula I gave you
does...

If they're not sorted by date, a Pivot Table would probably be your best
choice.

 

[JulieD]

you're welcome

perfect, thanks.