Formula for counting multiple cells
Hello,
I am struggling with formulas on my calculation sheet that references my main sheet. In the example below, I am trying to count every time my name appears (which could be in several different columns) if one column has a certain activity selected (in the example below this is "internal customer engagement".
Can someone figure out what is wrong with my formula below and how it should read?
=COUNTIFS({2023_MA Project Management System Range 1}, "Internal Customer Engagement", {2024 MA Activity Management System Range 3}, HAS(@cell, "Lindsay Megenhardt") + COUNTIFS({2024 MA Activity Management System Range 4}, HAS(@cell, "Lindsay Megenhardt")) + COUNTIFS({2024 MA Activity Management System Range 5}, HAS(@cell, "Lindsay Megenhardt")))
Answers
-
Quick question - will Lindsey be in a Contact type column in all of these instances, or Text? Or a mix of column types?
-
The columns with my name are populated using a Form that has a drop down menu with my name and others to choose from. Does that answer your question?
-
It looks like you're just missing a closing parentheses after the first COUNTIFS 🙂
=COUNTIFS(.... HAS()) < this one. We close off the HAS() but then also need to close off the COUNTIFS.
I assume you also need to filter by the first criteria in your other two COUNTIFS, is that correct? Try:
=COUNTIFS({2023_MA Project Management System Range 1}, "Internal Customer Engagement", {2024 MA Activity Management System Range 3}, HAS(@cell, "Lindsay Megenhardt")) + COUNTIFS({2023_MA Project Management System Range 1}, "Internal Customer Engagement", {2024 MA Activity Management System Range 4}, HAS(@cell, "Lindsay Megenhardt")) + COUNTIFS({2023_MA Project Management System Range 1}, "Internal Customer Engagement", {2024 MA Activity Management System Range 5}, HAS(@cell, "Lindsay Megenhardt"))
Keep in mind that if your name appears three times in the row with "Internal Customer Engagement" selected, this will count 3x.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!