Multiple Sums Depending on a description.

[Hanzo]

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!

 

[Guest]

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]

Well, I just tried, but the result is 0 in every case... :

 

[Hanzo]

I got it working! Sorry. It was My mistake. I misadjusted the ranges!
Thanks A lot!

 

[Ron Coderre]

Well..... Using your example, if you are getting zeros from the formul
I posted, then:

Col_A is formatted as TEXT
and/or
Col_A contains text that looks like numbers. Are there trailin
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,
Ro

 

[Hanzo]

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
AND J in column C

 

[Dave Peterson]

=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]

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?

 

[Dave Peterson]

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]

Yeah, I liked that too...
Thanks A lot!