Trouble with Countifs formula across sheets

I'm pulling data from one sheet into another to total a few items. I've attached screenshots. Sheet one is where the formulas are located, sheet 2 is the original data.

I'm using a helper column for the week and Countifs to count how many appointments meet the criteria. Here is my formula:

=COUNTIFS({Assigned Agent}, Agent@row, {Agent Claimed}, "Yes", {Week number}, [(Ref) Week Number]@row)

So I'm trying to count how many times an agent claimed an appointment each week by saying count assigned agents that match the agent name at this row, if Agent claimed = Yes (this is a drop-down in the original sheet 2) and if the week number matches the week number at this row.

I get back a 0, but this agent had 2 in this week.

Any ideas what might be wrong?

The second question, is it possible to pull data from a multi-select column if it meets one criterion? What I mean is can I count the number of times "Claimed" appears in the Appointment Status, no matter what else is there?

Thanks

Sheet 2.jpg

Sheet one.jpg

Find more posts tagged with

Accepted answers

Mark Cronk

Yes. Use the HAS function.

https://help.smartsheet.com/function/has

Glad you got it working.

Mark

All comments

Mark Cronk

Hi Andrea, Your syntax looks right. Try removing pieces of your COUNTIFS formula to determine which range and criteria are causing the problem. Try these. One of them should return 0.

=COUNTIFS({Assigned Agent}, Agent@row)

=COUNTIFS({Agent Claimed}, "Yes")

=COUNTIFS({Week number}, [(Ref) Week Number]@row)

My guess would be it's the week number. If it is, try {Week number}, value([(Ref) Week Number]@row)

Any luck?

Mark

Andrea Westrich

Hi @Mark Cronk, the formula was working; the data had an error in it that had not been corrected, so it did work finally.

However, do you know if it's possible to pull data from a multi-select column if it meets one criterion? What I mean is can I count the number of times "Claimed" appears in the Appointment Status, no matter what else is there?

Mark Cronk

Yes. Use the HAS function.

https://help.smartsheet.com/function/has

Glad you got it working.

Mark

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.