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 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, ", ")))
