Can I use COUNTIF and COUNTM in the same formula?


I am trying to count the number of selections in cells in a multi-select column (call it Column B) based on the values entered in Column A.

For example, IF Column A = "APPLE" how many total items have been selected in Column B for those rows.

Can this even be done?

I can get the value I need using COUNTM and selecting the specific cells myself, but when I need to add another row with "Apple" for example, I will have to go back and adjust my formula to include the new cell. I am trying to avoid doing that if at all possible.

Thanks to anyone who can help.

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @Cheryl Padgett ,

    I'm sure there's a way to make it work without a helper column, but the easiest way for me was to do this:

    Add a "Count" column (then hide it, if desired) with the formula: =COUNTM([column b]@row)

    Then in the column you want to total:

    =SUM(COLLECT(Count:Count, [Column B]:[Column B], HAS(@cell, [Column A]@row)))

    This translates to: Sum the Count column for only the rows where Column B contains the text that is in this row's Column A.

    Here's a screenshot of what I set up:

    Hope this helps! Let me know if it works for you.




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!