# want sumif function's range to evaluate 2 columns

1. ## 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

2. ## Re: want sumif function's range to evaluate 2 columns

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

will work

--

HTH

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

"Debgala" <Debgala@discussions.microsoft.com> wrote in message
news:CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com...
> 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

3. ## Re: 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:Q27>0)+(R4:R27>0)>0),H4:H27)

Hope this helps!

In article <CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com>,
"Debgala" <Debgala@discussions.microsoft.com> 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

4. ## Re: 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:Q27>0)*(R4:R27>0)*H4:H27)

Q *or* R
=SUMPRODUCT(((Q4:Q27>0)+(R4:R27>0)>0)*H4:H27)
--

HTH,

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

"Debgala" <Debgala@discussions.microsoft.com> wrote in message
news:CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com...
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

5. ## Re: 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:H27)
>
> will work
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Debgala" <Debgala@discussions.microsoft.com> wrote in message
> news:CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com...
> > 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

>
>
>

6. ## Re: want sumif function's range to evaluate 2 columns

"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:Q27>0)+(R4:R27>0)>0),H4:H27)
>
> Hope this helps!
>
> In article <CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com>,
> "Debgala" <Debgala@discussions.microsoft.com> 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

>

7. ## Re: 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" <Debgala@discussions.microsoft.com> wrote in message
news:E2CB0136-5CEC-433F-914D-BEA519653A12@microsoft.com...
> Thanks Bob - it works like a charm.
>
> "Bob Phillips" wrote:
>
> > =SUMIF(Q4:Q27,">0",H4:H27)+SUMIF(R4:R27,">0",H4:H27)
> >
> > will work
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Debgala" <Debgala@discussions.microsoft.com> wrote in message
> > news:CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com...
> > > 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

> >
> >
> >

8. ## RE: 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:Q27>0)+(R4:R27>0),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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1