SUMIF and list of possible critieria
 

How do I create a list that I can then use for SUMIF and COUNTIF formulas?

Instead of adding 10 different SUMIF formulas, I'm hoping to just reference
a list (with those 10 criteria) where only one of the list criteria has to be
satisfied.

For example, if I want to count things from MA or NY or CT, I could do three
separate COUNTIF formulas. But I'm hoping to somehow create a list (MA, NY,
CT) that I can then refer to in just one COUNTIF formula.

How do I do that?

SUMIF and list of possible critieria
 

List your criteria in the range H1:H3, then:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,H1:H3,0))))

Which is the equivalent of:

=COUNTIF(A1:A10,H1)+COUNTIF(A1:A10,H2)+COUNTIF(A1: A10,H3)

--
Biff
Microsoft Excel MVP


"blswes" wrote in message
...
How do I create a list that I can then use for SUMIF and COUNTIF formulas?

Instead of adding 10 different SUMIF formulas, I'm hoping to just
reference
a list (with those 10 criteria) where only one of the list criteria has to
be
satisfied.

For example, if I want to count things from MA or NY or CT, I could do
three
separate COUNTIF formulas. But I'm hoping to somehow create a list (MA,
NY,
CT) that I can then refer to in just one COUNTIF formula.

How do I do that?

SUMIF and list of possible critieria
 

Just counting those states

=SUMPRODUCT(COUNTIF(C1:C10,{"MA","NY","CT"}))

summing them

=SUMPRODUCT(SUMIF(C1:C10,{"MA","NY","CT"},D1:D10))

and another criteria

=SUMPRODUCT(--(B1:B10="x"),--(ISNUMBER(MATCH(C1:C10,{"MA","NY","CT"},0))),D1:D1 0)

--
__________________________________
HTH

Bob

"blswes" wrote in message
...
How do I create a list that I can then use for SUMIF and COUNTIF formulas?

Instead of adding 10 different SUMIF formulas, I'm hoping to just
reference
a list (with those 10 criteria) where only one of the list criteria has to
be
satisfied.

For example, if I want to count things from MA or NY or CT, I could do
three
separate COUNTIF formulas. But I'm hoping to somehow create a list (MA,
NY,
CT) that I can then refer to in just one COUNTIF formula.

How do I do that?