EXCEL Countifs

Copper Contributor

IF range A2:A147,="y" 

Count the number of times in Range N2:N147,=City1, City1. City 3 City 4, City 5, City 6 appears? 

I am having trouble with counting the multiple ranges and criteria?? 

4 Replies
Share a excel workbook and show your desired output.





So I am looking at A2:A147 if there is a "y" I want to count the city to come up how many from a city are coming - so this is easy Countif works fine for counting just one city. 

Result - Row 2 and 3 have a "y" so my count for Red Deer s/b 2 

The issue is when I have a few or more 5 cities and I am want to add the count for those coming together. The hidden issue maybe this will will not work as the range1 has blanks cells.  

Result if I want to count if I combine Countifs several cities so if I want to group Red Deer and Lacombe and a few other cities COUNTIFS result is coming back 0 I think because of the syntax or it sees range 1 id blank and comes back with zero for the value. 

@ChrisHH Lets try following formulas-


Group by cities.





@ChrisHH If you have Excel for MS365, one method of creating custom groups is with a delimiter. For example, if you wish to create regional groups for Lacombe, Red Deer and Sylvan Lake (group 1), as well as Sundre, Olds and Didsbury (group 2), input "Lacombe, Red Deer, Sylvan Lake" in cell F2 and "Sundre, Olds, Didsbury" in cell F3. The delimiter I've used in this example is a comma with a single space (", "). You can then use the SUM / COUNTIFS method with TEXTSPLIT to count multiple items in the range:


=SUM(COUNTIFS($A$2:$A$11, "y", $D$2:$D$11, TEXTSPLIT(F2, ", ")))


COUNTIFS with Multiple OR CriteriaCOUNTIFS with Multiple OR Criteria