SUMIF with 2 conditions

J

JBoyer

I'm trying to use a SUMIF function with one sum range and two seperate ranges
with seperate conditions.

For example: I'm trying to sum the cells in the "I" column where the "H"
Column is equal to "A" AND the "K" column is equal to "+".

This is one of the conditions but I don't know how to add the other.
=SUMIF($H$4:$H$1000,"A",$I$4:$I$1000)

The other would be
=SUMIF($K$4:$K$1000,"+",$I$4:$I$1000)

Thanks in advance for any help!
 
D

David Biddulph

You need SUMPRODUCT.
See numerous posting in this newsgroup for examples.
 
J

JBoyer

I tried your suggestion and I'm getting a #VALUE! ERROR. I'm not very
familiar with SUMPRODUCTS, I did however try swithing the "*" to "," thinking
that was a typo but it still had the same error.
 
D

Don Guillett

I just tested and it worked just fine. However this will ignore text in col
I

=SUMPRODUCT(--($H$4:$H$1000="A"),--($K$4:$K$1000="+"),$I$4:$I$1000)
 
T

T. Valko

Try it like this:

=SUMPRODUCT(--($H$4:$H$1000="A"),--($K$4:$K$1000="+"),$I$4:$I$1000)
 
J

JBoyer

Now it works, yes I guess I needed to ignore the text because some of the
cells in column "I" had text. Thanks for your help, and response to my
problem!
 
R

Richard Buttrey

Hi,

You don't say which version of XL you're using, but if you have 2007
then you could use the new SUMIFS() function, which allows additional
criteria.

HTH
 
S

ShaneDevenshire

Hi,

Here is the formula in 2007:

=SUMIFS(I$4:I$1000,H$4:H$1000,"A",K$4:K$1000,"+")
 
L

Linnet

Just a quick note to thank you and the Community for helping to solve a
formula problem I was having whilst trying to reduce the size of several
linked spreadsheets. I am a big fan of Excel and I happy to learn all the
time.
I had never even heard of SUMPRODUCT function and it has worked a treat.
Many thanks, I know where my first stop will be for any future problems.

Linnet
 
B

Bernd P

Hello Biff,

[Half-joking] Now what if I tell you that Sumproduct has proven to be
Excel's worst "time waster"?

This is because many people ask for just ONE example, but actually
quite often they need counts or sums for each occurring combination of
the criteria they mention.

Here we should come to pivot tables or - if you prefer auto updating
sheets - to my UDF's Pfreq resp. Sfreq:
http://www.sulprobil.com/html/listfreq.html

Have fun,
Bernd
 
J

JEB

Biff, thanks for the website...

I'm still running MSExcel 2003.. I'm still having trouble with the final
output. As in the xlDynamic examples... My return values are coming back as
zero, no matter what the array sets forth.

My "Column C" values are calculated values from other cells. Does that make
a difference?
 
T

T. Valko

My "Column C" values are calculated values
from other cells. Does that make a difference?

Possibly. What does the formula in column C look like? It may be returning
TEXT numbers instead of numeric numbers.
 
J

JEB

The formula is my "Column C" (Col AD) is: =(AB64*AC64*52)/1000

My SUMPRODUCT formula is:

=SUMPRODUCT((A7:A72=">=1")*AD7:AD72)

I fill in something on Column A and the cell with the SUMPROD formula
doesn't populate. It doesn't have an error, but it always has a zero.
 
T

T. Valko

=SUMPRODUCT((A7:A72=">=1")*AD7:AD72)

OK, here's what that formula is doing...

It's looking in the range A7:A72 for the *TEXT string* >=1. I'll bet you
want that to be:

=SUMPRODUCT((A7:A72>=1)*AD7:AD72)

Which can also be done with a simple SUMIF:

=SUMIF(A7:A72,">=1",AD7:AD72)

With SUMIF and COUNTIF (and SUMIFS and COUNTIFS in Excel 2007) you quote the
criteria operator but in other functions you don't.
 
J

JEB

Oh, that's it! Rookie mistake.

Thanks so much!

T. Valko said:
OK, here's what that formula is doing...

It's looking in the range A7:A72 for the *TEXT string* >=1. I'll bet you
want that to be:

=SUMPRODUCT((A7:A72>=1)*AD7:AD72)

Which can also be done with a simple SUMIF:

=SUMIF(A7:A72,">=1",AD7:AD72)

With SUMIF and COUNTIF (and SUMIFS and COUNTIFS in Excel 2007) you quote the
criteria operator but in other functions you don't.
 
J

JEB

Well, yes, I'm using the SUMPRODUCT because I have to look at more columns,
but this was just testing it for one column first.

Now I have
=SUMPRODUCT(($A$7:$A$71>=1)*($B$7:$B$71>=1)*($C$7:$C$71>=1)*($D$7:$D$71>=1)*($E$7:$E$71>=1)*($F$7:$F$71>=1)*($G$7:$G$71>=1)*($H$7:$H$71>=1)*($AE$7:$AE$71))

And while it worked for just A7:A71, it doesn't seem to be working for cols
B thru H. I"m sure the root cause is because it is returning a zero for at
least one of the columns and therefore not adding that row value.

So, is there a better way to do this? I'm scanning an array of cells
(A7:H71) and if any of them have a number >=1 in the cell, then it is to add
the corresponding value in column AE.

Is that then an OR operation like this:
=SUMPRODUCT(($A$7:$A$71>=1)+($B$7:$B$71>=1)+($C$7:$C$71>=1)+..*($AE$7:$AE$71))

That seems to count, then multiply by the final row. Hmmm, I've led myself
astray, I think.
 
T

T. Valko

Is that then an OR operation like this

If I understand what you want to do, it is, but you need to do it like this:

=SUMPRODUCT(--((A7:A71>=1)+(B7:B71>=1)+(C7:C71>=1)+(D7:D71>=1)+(E7:E71>=1)+(F7:F71>=1)+(G7:G71>=1)+(H7:H71>=1)>0),J7:J71)

See if this does what you want:

=SUMPRODUCT(--(MMULT(--(A7:H71>=1),{1;1;1;1;1;1;1;1})>0),AE7:AE71)

--
Biff
Microsoft Excel MVP


JEB said:
Well, yes, I'm using the SUMPRODUCT because I have to look at more
columns,
but this was just testing it for one column first.

Now I have:
=SUMPRODUCT(($A$7:$A$71>=1)*($B$7:$B$71>=1)*($C$7:$C$71>=1)*($D$7:$D$71>=1)*($E$7:$E$71>=1)*($F$7:$F$71>=1)*($G$7:$G$71>=1)*($H$7:$H$71>=1)*($AE$7:$AE$71))

And while it worked for just A7:A71, it doesn't seem to be working for
cols
B thru H. I"m sure the root cause is because it is returning a zero for
at
least one of the columns and therefore not adding that row value.

So, is there a better way to do this? I'm scanning an array of cells
(A7:H71) and if any of them have a number >=1 in the cell, then it is to
add
the corresponding value in column AE.

Is that then an OR operation like this:
=SUMPRODUCT(($A$7:$A$71>=1)+($B$7:$B$71>=1)+($C$7:$C$71>=1)+..*($AE$7:$AE$71))

That seems to count, then multiply by the final row. Hmmm, I've led myself
astray, I think.
 

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