Count the number of times values appear in a multiselect column - depending on a value in other cell

Amanrique

I want to count the number of times values appear in a multiselect column with the condition of a value appearing in a different cell . for example: we have a fruit multiselect column and day of the week when we bought the fruits. I want to count how many times apple appears in a fruit multi select column that we bought on Tuesdays. Or I want to count how many times I bought strawberries on Wednesdays. what would be the appropriate formula for this? let me know if this makes sense!

Jeff Reisman

@Amanrique

For criteria where you want to match a single distinct value in a multi-select cell, you need to use the HAS function inside your COUNTIFS. (I just very happily saw that help page for the HAS function has been updated - it was a haphazard copy of the page for CONTAINS, but now it contains some of the clarifications and re-writes I submitted for it. Still not perfect, but better.)

So for your sample question. If Fruit is multi-select, and Purchase Date is a date column:

=COUNTIFS([Fruit]:[Fruit], HAS(@cell, "apple"), [Purchase Date]:[Purchase Date], WEEKDAY(@cell) = 3)

The WEEKDAY function returns the day of the week as a number from 1-7 (Sunday to Saturday) for a date value. So if I did =WEEKDAY(TODAY()) I would get a 4, because today is Wednesday June 8th, 2022. In the formula above, we use WEEKDAY(@cell) because the "@cell" tells the system to evaluate each cell in the Purchase Date column by using the WEEKDAY function on it.

Jeff Reisman

@Amanrique

For criteria where you want to match a single distinct value in a multi-select cell, you need to use the HAS function inside your COUNTIFS. (I just very happily saw that help page for the HAS function has been updated - it was a haphazard copy of the page for CONTAINS, but now it contains some of the clarifications and re-writes I submitted for it. Still not perfect, but better.)

So for your sample question. If Fruit is multi-select, and Purchase Date is a date column:

=COUNTIFS([Fruit]:[Fruit], HAS(@cell, "apple"), [Purchase Date]:[Purchase Date], WEEKDAY(@cell) = 3)

The WEEKDAY function returns the day of the week as a number from 1-7 (Sunday to Saturday) for a date value. So if I did =WEEKDAY(TODAY()) I would get a 4, because today is Wednesday June 8th, 2022. In the formula above, we use WEEKDAY(@cell) because the "@cell" tells the system to evaluate each cell in the Purchase Date column by using the WEEKDAY function on it.