Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Median return for multiple critieria | Excel Discussion (Misc queries) | |||
Deleting Rows based on Column Critieria | Excel Discussion (Misc queries) | |||
Summing numbers in a list when they meet a critieria in another co | Excel Discussion (Misc queries) | |||
Retrieve and group row data by multiple critieria | Excel Worksheet Functions | |||
summing data that match critieria in a rnage | Excel Discussion (Misc queries) |