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!

Find more posts tagged with

Accepted answers

John Jonassen

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

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.