Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional subtotals
Thanks for that Roger.
Ken Johnson |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formula - language needed | Excel Worksheet Functions | |||
Conditional Formatting and Subtotals | Excel Discussion (Misc queries) | |||
How do I copy an outline w/ subtotals & paste just the subtotals | Excel Discussion (Misc queries) | |||
How do I use conditional formatting to change subtotals row format | Excel Worksheet Functions | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |