# sumif when criteria is a range

1. ## sumif when criteria is a range

I'm still an excel newbie--I searched the archives but couldn't find the

How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).

Thanks.

--
Message posted via http://www.officekb.com

2. ## Re: sumif when criteria is a range

One way:

=SUMPRODUCT(--(rng>=0),--(rng<10), rng)

another:

=COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")

a third

=COUNTIF(rng, "<10") - COUNTIF(rng, "<0")

In article <52E2906E1D7EC@OfficeKB.com>,
"jeremy via OfficeKB.com" <forum@OfficeKB.com> wrote:

> I'm still an excel newbie--I searched the archives but couldn't find the
>
> How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
>
> Thanks.

3. ## Re: sumif when criteria is a range

Sorry, I forgot to specify that I want to sum a second column if the first
meets the criteria range...

Eg. =SUMIF(\$BE\$9:\$BE\$272, 0=<X<10, (\$BF\$9:\$BF\$272))

Of course, 0=<X<10 doesn't work.....

Thanks....

JE McGimpsey wrote:
>One way:
>
> =SUMPRODUCT(--(rng>=0),--(rng<10), rng)
>
>another:
>
> =COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")
>
>a third
>
> =COUNTIF(rng, "<10") - COUNTIF(rng, "<0")
>
>> I'm still an excel newbie--I searched the archives but couldn't find the
>>
>> How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
>>
>> Thanks.

--
Message posted via http://www.officekb.com

4. ## Re: sumif when criteria is a range

The Sumproduct formula that you quoted in your earlier post will do the same

=SUMPRODUCT((\$W\$9:\$W\$272>=0)*(\$W\$9:\$W\$272<10)*\$W\$9:\$W\$272)

OR

=SUMIF(\$W\$9:\$W\$272,">0")-SUMIF(\$W\$9:\$W\$272,">=10")

--

HTH,

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

"jeremy via OfficeKB.com" <forum@OfficeKB.com> wrote in message
news:52E2906E1D7EC@OfficeKB.com...
I'm still an excel newbie--I searched the archives but couldn't find the

How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).

Thanks.

--
Message posted via http://www.officekb.com

5. ## Re: sumif when criteria is a range

John,

Countif ?
Confusing the OP's 2 posts?<g>
--

Regards,

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

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-EDD9F2.09404915082005@msnews.microsoft.com...
One way:

=SUMPRODUCT(--(rng>=0),--(rng<10), rng)

another:

=COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")

a third

=COUNTIF(rng, "<10") - COUNTIF(rng, "<0")

In article <52E2906E1D7EC@OfficeKB.com>,
"jeremy via OfficeKB.com" <forum@OfficeKB.com> wrote:

> I'm still an excel newbie--I searched the archives but couldn't find the
>
> How do I use sumif function when the criteria is a range (eg. >=0 to

<10 ).
>
> Thanks.

6. ## Re: sumif when criteria is a range

Again, you can use your original formula and just change the range that you
wish to total:

=SUMPRODUCT((\$BE\$9:\$BE\$272>=0)*(\$BE\$9:\$BE\$272<10)*\$BF\$9:\$BF\$272)

--

HTH,

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

"jeremy via OfficeKB.com" <forum@OfficeKB.com> wrote in message
news:52E2C179B8580@OfficeKB.com...
Sorry, I forgot to specify that I want to sum a second column if the first
meets the criteria range...

Eg. =SUMIF(\$BE\$9:\$BE\$272, 0=<X<10, (\$BF\$9:\$BF\$272))

Of course, 0=<X<10 doesn't work.....

Thanks....

JE McGimpsey wrote:
>One way:
>
> =SUMPRODUCT(--(rng>=0),--(rng<10), rng)
>
>another:
>
> =COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")
>
>a third
>
> =COUNTIF(rng, "<10") - COUNTIF(rng, "<0")
>
>> I'm still an excel newbie--I searched the archives but couldn't find the
>>
>> How do I use sumif function when the criteria is a range (eg. >=0 to

<10 ).
>>
>> Thanks.

--
Message posted via http://www.officekb.com

7. ## Re: sumif when criteria is a range

In article <#iXsoIboFHA.1048@tk2msftngp13.phx.gbl>,
"RagDyeR" <ragdyer@cutoutmsn.com> wrote:

> Countif ?

Yup - should have been SUMIF()

8. ## Re: sumif when criteria is a range

thanks--that was brillant...

RagDyeR wrote:
>The Sumproduct formula that you quoted in your earlier post will do the same
>
>=SUMPRODUCT((\$W\$9:\$W\$272>=0)*(\$W\$9:\$W\$272<10)*\$W\$9:\$W\$272)
>
>OR
>
>=SUMIF(\$W\$9:\$W\$272,">0")-SUMIF(\$W\$9:\$W\$272,">=10")
>
>I'm still an excel newbie--I searched the archives but couldn't find the
>
>How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
>
>Thanks.

--
Message posted via http://www.officekb.com

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