Using multiple criteria in SUMIF & COUNTIF?

  • Thread starter Thread starter ScubaJoe13bitem
  • Start date Start date
S

ScubaJoe13bitem

Any suggestions would be greatly appreciated.

I am trying to use multiple criteria with the SUMIF and COUNTI
functions. Assume a column of numbers that range from 10 to 300, wit
some numbers that repeat. I am trying to either count and sum a rang
of numbers, say beween 20 - 30 (using < and > multiple criteria), o
count and sum more than one specific number (using multiple
criteria).

I have tried every seperator I know of, and have tried adding tw
COUNTIF's in one long formula, all to no avail.

Does anyone know if it is possible to even use multiple criteria? I
so, how? Are there any other ways, such as nesting, other functions
etc.

Thank you in advanc
 
You could accomplish this either combining the SumIf's or CountIf's, or by
using the Sumproduct function.

For example, what's the total number of red and green balls in column A:

=Countif(A1:A100,"red")+Countif(A1:A100,"green")

=Sumproduct((A1:A100="red")+(A1:A100="green")
--

HTH,

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


message Any suggestions would be greatly appreciated.

I am trying to use multiple criteria with the SUMIF and COUNTIF
functions. Assume a column of numbers that range from 10 to 300, with
some numbers that repeat. I am trying to either count and sum a range
of numbers, say beween 20 - 30 (using < and > multiple criteria), or
count and sum more than one specific number (using multiple =
criteria).

I have tried every seperator I know of, and have tried adding two
COUNTIF's in one long formula, all to no avail.

Does anyone know if it is possible to even use multiple criteria? If
so, how? Are there any other ways, such as nesting, other functions,
etc.

Thank you in advance
 
Joe,

We have to use arcane-looking SUMPRODUCT constructs.
Count:
=SUMPRODUCT((A2:A65536>=20)*(A2:A65536<=30))
Sum:
=SUMPRODUCT((A2:A65536>=20)*(A2:A65536<=30)*A2:A65536)
 
You already got 2 answers, get a newsreader instead!

=COUNT(Range,">=20")-COUNT(Range,">30")

will count the numbers 20 - 30

if you don't want to include 30 use >= instead of > in the second countif

this will also work

=SUMPRODUCT(--(Range>=30),--(Range<=30))
 
I was trying to do the samer thing and I think I figured it out b
messing around with another suggestion someone gave on another foru
for comparing columns:

=SUMPRODUCT(--(A1:A1000>=11),--(A1:A1000<=20))

That should count anything from 11-20 in the "A" column.

-Jame
 
Back
Top