Multiple Sums Depending on a description.

H

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!
 
R

Ron Coderre

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
 
R

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
 
H

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
 
D

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.
 
H

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?
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top