Newbie: Using COUNTIF to calculate uses of a text string on another sheet


I think I've reached the limits of my self-teaching and could use a little guidance. I have a sheet called Assigned Categories that includes a Category column. That column is a multi-select drop down list and we've made multiple selections in each row.

I have a separate sheet called Statistics with two columns: Name and Total. Name contains the same drop down list as Category in the previous sheet, but only allows a single selection.

I want a formula in the Total column that counts the number of times Statistics' [Name]@row appears in the entirety of Assigned Categories>Category. It shouldn't care if other selections were made as well, it's just counting the number of checks for a specific choice.

My formula:

=COUNTIF({Assigned Categories Range 1}, [Name]@row)

I've tried multiple online suggestions for rewriting this, but generally end up with a 0 count when I know there should be matches.

Our old pal CHATGPT suggested:

=COUNTIFS({Assigned Categories Range 1}, [Name]@row, {Assigned Categories Range 1}, [Name]@row)

And this returns a 0 count for all.

Reddit suggested:

=COUNTIF({Assigned Categories Range 1}, CONTAINS([CATEGORY NAME]@row, {Assigned Categories Range 1}))

And this just gets me 0.

Suggestions - I mean, outside of don't rely on CHATGPT to teach me anything?


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!