SUMIF with 2 conditions

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

 

[Don Guillett]

try
=SUMproduct(($H$4:$H$1000="A")*($K$4:$K$1000="+")*$I$4:$I$1000)

 

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

 

[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. Valko]

Try it like this:

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

 

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

 

[Don Guillett]

Glad to help

 

[ShaneDevenshire]

Hi,

Here is the formula in 2007:

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

 

[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

 

[T. Valko]

Tons of info on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html

The most dynamic counting and summing function there is!

 

[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

 

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

 

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

 

[JEB]

Oh, that's it! Rookie mistake.

Thanks so much!

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.

 

[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. 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)+(D771>=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

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. Valko]

Ooops! I used the wrong sum range in the long formula:

...+(H7:H71>=1)>0),J7:J71)

Should be:

....+(H7:H71>=1)>0),AE7:AE71)

--
Biff
Microsoft Excel MVP

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)+(D771>=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)

 

[JEB]

YES! Wow, Biff! That's awesome!

Thank you soooo much!

Ooops! I used the wrong sum range in the long formula:

...+(H7:H71>=1)>0),J7:J71)

Should be:

....+(H7:H71>=1)>0),AE7:AE71)