# 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

• 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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• 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?

• Yes. Use the HAS function.

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