Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Leigh Ann
 
Posts: n/a
Default 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   Report Post  
N Harkawat
 
Posts: n/a
Default

=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   Report Post  
Duke Carey
 
Posts: n/a
Default

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   Report Post  
Leigh Ann
 
Posts: n/a
Default

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.

  #5   Report Post  
Mike
 
Posts: n/a
Default

Duke,

Can you explain the purpose of the double negatives?

Thanks.



  #6   Report Post  
Domenic
 
Posts: n/a
Default

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   Report Post  
Mike
 
Posts: n/a
Default

Got it! Thanks for the post.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 04:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"