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


Best Answer

Answers

  • Mark Cronk
    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


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

  • Andrea Westrich
    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
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Yes. Use the HAS function.

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

    Glad you got it working.

    Mark


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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!