SUMIF and list of possible critieria

[blswes]

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?

 

[T. Valko]

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)

 

[Bob Phillips]

Just counting those states

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

summing them

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

and another criteria

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