Forum Discussion
EXCEL Countifs
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??
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 Criteria