Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUM IF and two variables
I have tried...without success.
A B C D dog 1 brown 2 cat 1 brown dog 2 black 4 cat 3 blue For example: I need a formula that says if column D has a value in it AND column A = "dog", then sum column B. I have tried versions of the SUMPRODUCT function but cannot solve it. The problem is that column D cells contain a formula, I think. Any help is greatly appreciated. |
#2
|
|||
|
|||
=SUMPRODUCT(--(D1:D4<""),--(A1:A4="dog"),(B1:B4))
"Leigh Ann" wrote in message ... I have tried...without success. A B C D dog 1 brown 2 cat 1 brown dog 2 black 4 cat 3 blue For example: I need a formula that says if column D has a value in it AND column A = "dog", then sum column B. I have tried versions of the SUMPRODUCT function but cannot solve it. The problem is that column D cells contain a formula, I think. Any help is greatly appreciated. |
#3
|
|||
|
|||
If the formula in D is returning an empty string (""), then this will work
=SUMPRODUCT(--(A32:A35="dog"),--(LEN(D32:D35)0),B32:B35) If it's returning a 0 that is formatted to not show then =SUMPRODUCT(--(A1:A5="dog"),--(D1:D50),B1:B5) "Leigh Ann" wrote: I have tried...without success. A B C D dog 1 brown 2 cat 1 brown dog 2 black 4 cat 3 blue For example: I need a formula that says if column D has a value in it AND column A = "dog", then sum column B. I have tried versions of the SUMPRODUCT function but cannot solve it. The problem is that column D cells contain a formula, I think. Any help is greatly appreciated. |
#4
|
|||
|
|||
Thanks, Duke - that worked!
"Duke Carey" wrote: If the formula in D is returning an empty string (""), then this will work =SUMPRODUCT(--(A32:A35="dog"),--(LEN(D32:D35)0),B32:B35) If it's returning a 0 that is formatted to not show then =SUMPRODUCT(--(A1:A5="dog"),--(D1:D50),B1:B5) "Leigh Ann" wrote: I have tried...without success. A B C D dog 1 brown 2 cat 1 brown dog 2 black 4 cat 3 blue For example: I need a formula that says if column D has a value in it AND column A = "dog", then sum column B. I have tried versions of the SUMPRODUCT function but cannot solve it. The problem is that column D cells contain a formula, I think. Any help is greatly appreciated. |
#6
|
|||
|
|||
In article . com,
"Mike" wrote: Can you explain the purpose of the double negatives? Have a look at the following... http://www.mcgimpsey.com/excel/formulae/doubleneg.html |
#7
|
|||
|
|||
Got it! Thanks for the post.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|