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

Options

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!


Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!