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" <[email protected]> wrote in message
news:[email protected]...
>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 <[email protected]>,
"RockyMountains" <[email protected]> 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 <[email protected]>,
> "RockyMountains" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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 <[email protected]>,
"RockyMountains" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in
> >> message
> >> news:[email protected]...
> >> >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 <[email protected]>,
> "RockyMountains" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in
>> >> message
>> >> news:[email protected]...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>