Using a countif formula across sheets
Hi,
I keep getting different error messages when I try to use a countif that draws from different sheets. My goal is to keep a running count of how many calls each team-member has taken this year. We usually use up to 4 sheets a year tracking calls and I want to track the total number. This is what I am entering: ="Bocquet =" + COUNTIFS({7.1 Staff}, "Bocquet", {7.2 Staff}, "Bocquet") and I am given the 'incorrect argument' message, when I add brackets,="Bocquet =" + COUNTIFS({7.1 Staff}, "Bocquet", [{7.2 Staff}, "Bocquet"]), I get the 'unparseable' message. Does anyone see my errors? Can this be done?
Thank you all in advance, the community has never let me down!
Best Answer
-
Have you tried =COUNTIF({7.1 Staff}, "Bocquet")+COUNTIF({7.2 Staff}, "Bocquet")?
Answers
-
Have you tried =COUNTIF({7.1 Staff}, "Bocquet")+COUNTIF({7.2 Staff}, "Bocquet")?
-
Hi John,
Thank you for working on that for me. I just tried your formula and got the invalid operation error message, I tried a couple of versions and re-referencing my sheet but still get invalid operation. Not sure which is harder to solve, unparseable or invalid operation!
Thanks
Michele
-
Hi again John,
Your suggestion worked, this is how I got there: ="Bocquet: " + (COUNTIF({7.1 Staff}, "Bocquet") + COUNTIF({7.2 Staff}, "Bocquet") + COUNTIF({7.3_SY20-21_Hotline_Tracker Range 1}, "Bocquet")). The refinement came from the data person on my team. I owe you both!
Thanks a million!
Michele
-
That's great, @Michele @ My School DC ! Just out of curiosity, are you doing a concatenate with that ="Bocquet: " at the beginning? So your result would actually be "Bocquet: 15" or something like that?
-
Exactly, that way I only need one cell!
-
Wonderful!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 58 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!