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.

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

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.

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.

Duke,

Can you explain the purpose of the double negatives?

Thanks.

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

Got it! Thanks for the post.