SUMIF and list of possible critieria

B

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

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)
 
B

Bob Phillips

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:D10)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMIF with criteria "<>" & "=" 4
Copying and pasting with SUMIF and COUNTIF 3
Combine Sumif and Countif 3
Column Average Given 2 Criteria 4
How do I use countif like sumif ie (range,criteria,count_range) 1
SUMIF/COUNTIF with missing data 4
Countif/Sumif 2
combine countif and sumproduct? 2

Top