| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sums Depending on a description.
I really don't know how easy or difficult it might be, but here it is: I have something like this (example) : -------------------------------------- | A | B | C | D | -------------------------------------- 1 | 15| W1 | | | 2 | 5| W2 | | | 3 | 7| W1 | | | 4 | 9| W3 | | | 5 | 21| W4 | | | 6 | 14| W2 | | | -------------------------------------- Now, I want to have the result of all the W1 in D1, All W2 in D2, All W3 in D3 and all W4 in D4 without having to create any additional SUMs somewhere else to discriminate and then get the result (e.g. =IF($B2="W2",$A2,0) and copying the formula all along the column, then =SUM(xx:xx) and get the result.) Can it be done? Thanks a lot for your help! -- Hanzo ------------------------------------------------------------------------ Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955 View this thread: http://www.excelforum.com/showthread...hreadid=496516 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sums Depending on a description.
Here's something to work with:
D1: =SUMIF($B$1:$B$10,"W"&ROW(),$A$1:$A$10) Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Hanzo" wrote: I really don't know how easy or difficult it might be, but here it is: I have something like this (example) : -------------------------------------- | A | B | C | D | -------------------------------------- 1 | 15| W1 | | | 2 | 5| W2 | | | 3 | 7| W1 | | | 4 | 9| W3 | | | 5 | 21| W4 | | | 6 | 14| W2 | | | -------------------------------------- Now, I want to have the result of all the W1 in D1, All W2 in D2, All W3 in D3 and all W4 in D4 without having to create any additional SUMs somewhere else to discriminate and then get the result (e.g. =IF($B2="W2",$A2,0) and copying the formula all along the column, then =SUM(xx:xx) and get the result.) Can it be done? Thanks a lot for your help! -- Hanzo ------------------------------------------------------------------------ Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955 View this thread: http://www.excelforum.com/showthread...hreadid=496516 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sums Depending on a description.
Well, I just tried, but the result is 0 in every case... :( -- Hanzo ------------------------------------------------------------------------ Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955 View this thread: http://www.excelforum.com/showthread...hreadid=496516 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sums Depending on a description.
I got it working! Sorry. It was My mistake. I misadjusted the ranges! Thanks A lot! -- Hanzo ------------------------------------------------------------------------ Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955 View this thread: http://www.excelforum.com/showthread...hreadid=496516 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sums Depending on a description.
Well..... Using your example, if you are getting zeros from the formula I posted, then: Col_A is formatted as TEXT and/or Col_A contains text that looks like numbers. Are there trailing spaces? and/or Col_B may LOOK like W1, W2, etc but it may also contain extra spaces. Check for those errors and let us know what you find. Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=496516 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sums Depending on a description.
Lol... You are going to kill me! :) What happens if I want to have another criteria selection? Like for example sum everything from column A that has W1 in column B AND J in column C? -- Hanzo ------------------------------------------------------------------------ Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955 View this thread: http://www.excelforum.com/showthread...hreadid=496516 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sums Depending on a description.
=sumproduct(A1:A10,--(b1:b10="W1"),--(c1:c10="J"))
(adjust the ranges (all three must match), but don't use the whole column.) =sumproduct() likes to work with numbers. The -- converts True's and False's to 1's and 0's. Hanzo wrote: Lol... You are going to kill me! :) What happens if I want to have another criteria selection? Like for example sum everything from column A that has W1 in column B AND J in column C? -- Hanzo ------------------------------------------------------------------------ Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955 View this thread: http://www.excelforum.com/showthread...hreadid=496516 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sums Depending on a description.
Thanks A Lot! I found another way to do it: =SUM(IF(($B$2:$B$13="W")*($C$2:$C$13="X"),$A$2:$A$ 13)) But I had to make it an ARRAY FORMULA... Which way is better to use? -- Hanzo ------------------------------------------------------------------------ Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955 View this thread: http://www.excelforum.com/showthread...hreadid=496516 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sums Depending on a description.
I like the =sumproduct() version--simply because I don't have to remember (or
make sure the end user remembers) to use ctrl-shift-enter. Hanzo wrote: Thanks A Lot! I found another way to do it: =SUM(IF(($B$2:$B$13="W")*($C$2:$C$13="X"),$A$2:$A$ 13)) But I had to make it an ARRAY FORMULA... Which way is better to use? -- Hanzo ------------------------------------------------------------------------ Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955 View this thread: http://www.excelforum.com/showthread...hreadid=496516 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sums Depending on a description.
Yeah, I liked that too... Thanks A lot! :) -- Hanzo ------------------------------------------------------------------------ Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955 View this thread: http://www.excelforum.com/showthread...hreadid=496516 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto fill multiple cells depending on single cell value | Excel Worksheet Functions | |||
| Help: Multiple filters with mutually exclusive items | Excel Discussion (Misc queries) | |||
| Req: How to create multiple mutually exclusive filters | Excel Worksheet Functions | |||
| Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
| Excell will not add multiple sums together, what do I have turned. | Excel Discussion (Misc queries) | |||
Linear Mode
