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

Hinkbu

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?

Carson Penticuff

Give this one a try.

=IF([Name]@row <> "", COUNTIFS({Assigned Categories Range 1}, HAS(@cell, [Name]@row)), "")

Also, CHATGPT is great, if you keep in mind its limitations.

Carson Penticuff

Give this one a try.

=IF([Name]@row <> "", COUNTIFS({Assigned Categories Range 1}, HAS(@cell, [Name]@row)), "")

Also, CHATGPT is great, if you keep in mind its limitations.

Hinkbu

This was exactly the magic. Thank you so much. I don't know how I would have gotten there on my own, but one day... ONE DAY!