I wanted sumif to evaluate a range of cells over 2 columns.
My formula looked like this
=SUMIF(Q4:R27,"0",H4:H27)
Oddly enough, it works if there is a value 0 in column Q,
or in both columns Q and R,
but will not work if there is a value 0 in column R but no value 0 in
column Q.

How can I make it work? (And if anyone has time, why doesn't it work the
way it is now?)

Thanks,
Deb

=SUMIF(Q4:Q27,"0",H4:H27)+SUMIF(R4:R27,"0",H4:H2 7)

will work

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debgala" wrote in message
...
I wanted sumif to evaluate a range of cells over 2 columns.
My formula looked like this
=SUMIF(Q4:R27,"0",H4:H27)
Oddly enough, it works if there is a value 0 in column Q,
or in both columns Q and R,
but will not work if there is a value 0 in column R but no value 0 in
column Q.

How can I make it work? (And if anyone has time, why doesn't it work the
way it is now?)

Thanks,
Deb

Assuming that you want to sum the values in Column H where the
corresponding values in Column Q or Column R are greater than zero,
try...

=SUMPRODUCT(--((Q4:Q270)+(R4:R270)0),H4:H27)

Hope this helps!

In article ,
"Debgala" wrote:

I wanted sumif to evaluate a range of cells over 2 columns.
My formula looked like this
=SUMIF(Q4:R27,"0",H4:H27)
Oddly enough, it works if there is a value 0 in column Q,
or in both columns Q and R,
but will not work if there is a value 0 in column R but no value 0 in
column Q.

How can I make it work? (And if anyone has time, why doesn't it work the
way it is now?)

Thanks,
Deb

From just looking at your formula, I could guess that you want *both*
Columns Q & R to be greater then zero before adding the value in Column H.

However, I did say *guess*.

Could it also be Q *OR* R greater then zero?

So, is it Q *AND* R
Or is it Q *OR* R ?

And which way should Sumif() translate it?

Q *and* R
=SUMPRODUCT((Q4:Q270)*(R4:R270)*H4:H27)

Q *or* R
=SUMPRODUCT(((Q4:Q270)+(R4:R270)0)*H4:H27)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Debgala" wrote in message
...
I wanted sumif to evaluate a range of cells over 2 columns.
My formula looked like this
=SUMIF(Q4:R27,"0",H4:H27)
Oddly enough, it works if there is a value 0 in column Q,
or in both columns Q and R,
but will not work if there is a value 0 in column R but no value 0 in
column Q.

How can I make it work? (And if anyone has time, why doesn't it work the
way it is now?)

Thanks,
Deb

Thanks Bob - it works like a charm.

"Bob Phillips" wrote:

=SUMIF(Q4:Q27,"0",H4:H27)+SUMIF(R4:R27,"0",H4:H2 7)

will work

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debgala" wrote in message
...
I wanted sumif to evaluate a range of cells over 2 columns.
My formula looked like this
=SUMIF(Q4:R27,"0",H4:H27)
Oddly enough, it works if there is a value 0 in column Q,
or in both columns Q and R,
but will not work if there is a value 0 in column R but no value 0 in
column Q.

How can I make it work? (And if anyone has time, why doesn't it work the
way it is now?)

Thanks,
Deb

Thanks Domenic - your answer also works like a charm.

"Domenic" wrote:

Assuming that you want to sum the values in Column H where the
corresponding values in Column Q or Column R are greater than zero,
try...

=SUMPRODUCT(--((Q4:Q270)+(R4:R270)0),H4:H27)

Hope this helps!

In article ,
"Debgala" wrote:

I wanted sumif to evaluate a range of cells over 2 columns.
My formula looked like this
=SUMIF(Q4:R27,"0",H4:H27)
Oddly enough, it works if there is a value 0 in column Q,
or in both columns Q and R,
but will not work if there is a value 0 in column R but no value 0 in
column Q.

How can I make it work? (And if anyone has time, why doesn't it work the
way it is now?)

Thanks,
Deb

Really?

Mine and Domenic's can't both work like a charm, as they will return
different results. Mine is an AND condition, Domenic's is an OR. If say Q4
and R4 are 0, mine will add H4 twice, Domenic's just once.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debgala" wrote in message
...
Thanks Bob - it works like a charm.

"Bob Phillips" wrote:

=SUMIF(Q4:Q27,"0",H4:H27)+SUMIF(R4:R27,"0",H4:H2 7)

will work

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debgala" wrote in message
...
I wanted sumif to evaluate a range of cells over 2 columns.
My formula looked like this
=SUMIF(Q4:R27,"0",H4:H27)
Oddly enough, it works if there is a value 0 in column Q,
or in both columns Q and R,
but will not work if there is a value 0 in column R but no value 0
in
column Q.

How can I make it work? (And if anyone has time, why doesn't it work
the
way it is now?)

Thanks,
Deb

Hi,

It seems you want to sum the range if any one of columns (Q or R) have a 0.
You may also try the following array formula (Ctrl+Shift+Enter)

=sum(if((Q4:Q270)+(R4:R270),H4:H27))

Regards,

Ashish Mathur

"Debgala" wrote:

I wanted sumif to evaluate a range of cells over 2 columns.
My formula looked like this
=SUMIF(Q4:R27,"0",H4:H27)
Oddly enough, it works if there is a value 0 in column Q,
or in both columns Q and R,
but will not work if there is a value 0 in column R but no value 0 in
column Q.

How can I make it work? (And if anyone has time, why doesn't it work the
way it is now?)

Thanks,
Deb