SUMIF with 2 conditions

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


"T. Valko" wrote in message
...
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


"JEB" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP

YES! Wow, Biff! That's awesome!

Thank you soooo much!

"T. Valko" wrote:

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


"T. Valko" wrote in message
...
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


"JEB" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP





.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JEB" wrote in message
...
YES! Wow, Biff! That's awesome!

Thank you soooo much!

"T. Valko" wrote:

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


"T. Valko" wrote in message
...
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


"JEB" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP





.