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
-
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.
Answers
-
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.
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!