Using a countif formula across sheets

Michele @ My School DC

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!

John Jonassen

Have you tried =COUNTIF({7.1 Staff}, "Bocquet")+COUNTIF({7.2 Staff}, "Bocquet")?

John Jonassen

Have you tried =COUNTIF({7.1 Staff}, "Bocquet")+COUNTIF({7.2 Staff}, "Bocquet")?

Michele @ My School DC

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

Michele @ My School DC

https://community.smartsheet.com/discussion/comment/267262#Comment_267262

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

John Jonassen

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?

Michele @ My School DC

Exactly, that way I only need one cell!

John Jonassen

Wonderful!