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

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

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

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

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

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

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

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

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

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