want sumif function's range to evaluate 2 columns
 

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

want sumif function's range to evaluate 2 columns
 

=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

want sumif function's range to evaluate 2 columns
 

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

want sumif function's range to evaluate 2 columns
 

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

want sumif function's range to evaluate 2 columns
 

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

want sumif function's range to evaluate 2 columns
 

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

want sumif function's range to evaluate 2 columns
 

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

want sumif function's range to evaluate 2 columns
 

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