Conditional subtotals

**tx12345** · 12-11-2005, 02:56 AM

I have a subtotal question

Let's say I have this set of data
A..................B..........C
date_______amt_ sub
12/1/05____ 1000
12/1/05____ 2000
12/1/05____ 5000 7000
12/3/05____ 2000
12/3/05____ 9000 11000
12/6/05____ 1000 1000
12/7/05____ 4000
12/7/05____ 2000 6000

So we see a subtotal according to the date, where the total values in chronological order are calculated to be

12/1/05 7000
12/3/05 11000
12/6/05 1000
12/7/05 6000

What sort of formula, then, do I put in column C that subtotals values in B according to the date in A?

Many thanks

Ken Johnson · 12-11-2005, 05:15 AM

Hi tx12345,

This worked for me:

=IF(A3=A2,"",SUM(OFFSET(B2,1-COUNTIF(A:A,A2),0,COUNTIF(A:A,A2),1)))

Someone might have a shorter solution, they usually do.

Paste this into B2 then fill it down to the bottom of your list.

You might also want to look into Excel's automatic subtotals in Help.

Ken Johnson

Ken Johnson · 12-11-2005, 05:25 AM

Hi tx12345'
Call me a pillock!
Don't paste the formula into B2, paste it into C2.
Doubtless you would have corrected my blunder.
Sorry about that.

Ken Johnson

Roger Govier · 12-11-2005, 05:30 AM

Hi

One way
=IF(A2=A3,"",SUMPRODUCT(--($A$2:$A$9=A2),$B$2:$B$9))
Change ranges to suit.

Incidentally, I make the total for 12/1/05 8000, not 7000.

Regards

Roger Govier

tx12345 wrote:
> I have a subtotal question
>
> Let's say I have this set of data
> A..................B..........C
> date_______amt_ sub
> 12/1/05____ 1000
> 12/1/05____ 2000
> 12/1/05____ 5000 7000
> 12/3/05____ 2000
> 12/3/05____ 9000 11000
> 12/6/05____ 1000 1000
> 12/7/05____ 4000
> 12/7/05____ 2000 6000
>
> So we see a subtotal according to the date, where the total values in
> chronological order are calculated to be
>
> 12/1/05 7000
> 12/3/05 11000
> 12/6/05 1000
> 12/7/05 6000
>
> What sort of formula, then, do I put in column C that subtotals values
> in B according to the date in A?
>
> Many thanks
>
>

Ken Johnson · 12-11-2005, 05:55 AM

See what I mean!
Roger's formula is shorter!
One day soon I'm gonna get my brain around that nifty little SUMPRODUCT
function!
I wonder Roger, can A:A be used in your formula instead of
$A$2:$A$whatever?
I was originally using $A$2:$A$9 and was going to tell tx12345 to
change it to suit the table, then I noticed A:A gives the same result
in my suggested formula. I know it's not important, I'm just curious.

Ken Johnson

Ken Johnson · 12-11-2005, 06:10 AM

Hi Roger,
Don't bother, I tried it myself. A:A in the SUMPRODUCT gives the #NUM
error.
This is a first for me, I've managed to come up with a formula that has
an advantage over the SUMPRODUCT version, my formula doesn't need any
address adjustments.

Ken Johnson

Roger Govier · 12-11-2005, 06:15 AM

Hi Ken

Unfortunately, Sumproduct will not take whole columns as arguments, but,
rather perversely, it will take whole rows (1:1).
You have to specify the range, it could be $A$2:$A$65536, or anything shorter.
Personally, I try to use dynamic named ranges that apply to the set of data
required rather than using cells references.

Bob Phillips has a very good treatise on Sumproduct at his site which you
will find very useful
http://xldynamic.com/source/xld.SUMPRODUCT.html

Regards

Roger Govier

Ken Johnson wrote:
> See what I mean!
> Roger's formula is shorter!
> One day soon I'm gonna get my brain around that nifty little SUMPRODUCT
> function!
> I wonder Roger, can A:A be used in your formula instead of
> $A$2:$A$whatever?
> I was originally using $A$2:$A$9 and was going to tell tx12345 to
> change it to suit the table, then I noticed A:A gives the same result
> in my suggested formula. I know it's not important, I'm just curious.
>
> Ken Johnson
>

Aladin Akyurek · 12-11-2005, 06:40 AM

Assuming that A1:B9 houses the data of interest and the dates from A2 on
are in ascending order...

In C2 enter & copy down:

=IF((A2<>"")*(A2<>A3),SUMIF($A$2:$A$9,A2,$B$2:$B$9),"")

If you are on Excel 2003, make of A1:C9 a list by means of
Data|List|Create List so that with every new entry the formula gets
copied down automatically.

tx12345 wrote:
> I have a subtotal question
>
> Let's say I have this set of data
> A..................B..........C
> date_______amt_ sub
> 12/1/05____ 1000
> 12/1/05____ 2000
> 12/1/05____ 5000 7000
> 12/3/05____ 2000
> 12/3/05____ 9000 11000
> 12/6/05____ 1000 1000
> 12/7/05____ 4000
> 12/7/05____ 2000 6000
>
> So we see a subtotal according to the date, where the total values in
> chronological order are calculated to be
>
> 12/1/05 7000
> 12/3/05 11000
> 12/6/05 1000
> 12/7/05 6000
>
> What sort of formula, then, do I put in column C that subtotals values
> in B according to the date in A?
>
> Many thanks
>
>

Ken Johnson · 12-11-2005, 07:15 AM

Hi Aladin,

Nice one!
Also works as =IF((A2<>"")*(A2<>A3),SUMIF(A:A,A2,B:B),"")
Shorter still!
I've never bothered with Excel's Lists stuff, but after reading your
reply it looks like I've been depriving myself of a handy feature.
Thank you.

Ken Johnson