Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tx12345
 
Posts: n/a
Default Conditional subtotals


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


--
tx12345
------------------------------------------------------------------------
tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=492503

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default Conditional subtotals

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default Conditional subtotals

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Conditional subtotals

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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default Conditional subtotals

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default Conditional subtotals

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Conditional subtotals

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

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default Conditional subtotals

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


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default Conditional subtotals

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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default Conditional subtotals

Thanks for that Roger.

Ken Johnson



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tx12345
 
Posts: n/a
Default Conditional subtotals


Ken Johnson Wrote:
Thanks for that Roger.

Ken Johnson



Excellent thread. Will PM all who helped with finished product. Thanks
again.

tx


--
tx12345
------------------------------------------------------------------------
tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=492503

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formula - language needed gamebird Excel Worksheet Functions 7 October 10th 05 11:48 PM
Conditional Formatting and Subtotals Mary Ann Excel Discussion (Misc queries) 2 August 10th 05 12:09 PM
How do I copy an outline w/ subtotals & paste just the subtotals av Excel Discussion (Misc queries) 1 June 20th 05 11:35 PM
How do I use conditional formatting to change subtotals row format Ken Peterson Excel Worksheet Functions 3 March 24th 05 05:58 PM
Conditional formatting not available in Excel BAB Excel Discussion (Misc queries) 2 January 1st 05 04:33 PM


All times are GMT +1. The time now is 11:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"