|
Cumulative Total
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 |
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 "RockyMountains" wrote in message ... 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 |
One way:
D2: =SUMIF($A$2:A2,A2,$C$2:C2) Copy down as far as necessary. In article , "RockyMountains" wrote: 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 |
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? "JE McGimpsey" wrote: One way: D2: =SUMIF($A$2:A2,A2,$C$2:C2) Copy down as far as necessary. In article , "RockyMountains" wrote: 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 |
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. "JulieD" wrote: 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 "RockyMountains" wrote in message ... 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 |
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. In article , "RockyMountains" wrote: 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? "JE McGimpsey" wrote: One way: D2: =SUMIF($A$2:A2,A2,$C$2:C2) Copy down as far as necessary. |
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 "RockyMountains" wrote in message ... 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. "JulieD" wrote: 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 "RockyMountains" wrote in message ... 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 |
perfect, thanks.
"JulieD" wrote: 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 "RockyMountains" wrote in message ... 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. "JulieD" wrote: 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 "RockyMountains" wrote in message ... 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 |
Perfect, thanks.
"JE McGimpsey" wrote: 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. In article , "RockyMountains" wrote: 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? "JE McGimpsey" wrote: One way: D2: =SUMIF($A$2:A2,A2,$C$2:C2) Copy down as far as necessary. |
you're welcome
"RockyMountains" wrote in message ... perfect, thanks. "JulieD" wrote: 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 "RockyMountains" wrote in message ... 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. "JulieD" wrote: 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 "RockyMountains" wrote in message ... 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 |
| All times are GMT +1. The time now is 01:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com