gwen.pino78261 ✭✭✭✭
edited 12/09/19 in Formulas and Functions

I am trying to use a COUNTIFS formula and it is returning a zero, even though it should return a one...I think it is user error...I checked the reference sheet and it refers to all the columns on the sheet...

=COUNTIFS({SVC Support Priority Range 1}, "[Urgent - Drop Everything]", {SVC Support Priority Range 1}, "In Progress")



  • L_123
    L_123 ✭✭✭✭✭✭

    Did you mean:


    =COUNTIFS({SVC Support Priority Range 1}, [Urgent - Drop Everything]@row, {SVC Support Priority Range 1}, "In Progress")


    You should always name your other sheet references. It makes troubleshooting much easier.

  • gwen.pino78261
    gwen.pino78261 ✭✭✭✭

    I tried your formula and I got an error - #unparseable

    I am attaching my steps and screen captures to try and better explain thanks

    Step 1 -

    The "Countifs" image is the sheet Source

    Step 2 -

    Setting up Formula - Countifs

    Range 1 = SVC Support Priority Range 1 – used for both criterion

                    Includes all columns see below – "Countifs Sheet Reference Name" image

    Criterion 1 = {SVC Support Priority Range 1}, "[Urgent - Drop Everything]"

    Criterion 2 = {SVC Support Priority Range 1}, "New"

    Step 3 -

    CountIFS Formula used:

    • =COUNTIFS({SVC Support Priority Range 1}, "[Urgent - Drop Everything]", {SVC Support Priority Range 1}, "In Progress")
    • Results are 0 but there is actually 1 that should have been counted see "Countifs_metric" image




  • L_123
    L_123 ✭✭✭✭✭✭
    edited 09/13/19

    Ok. Couple things.

    1. Your references need to just be 1 column, you have many columns referenced as range 1. Your first range should be the entire column Priority.  Your second range should be the entire column Status.

    *To select an entire column, simply click on the header of the individual column. This will create a dynamic reference that will grow with the sheet.

    2. Reference a text value as being in quotations. "Urgent- Drop Everything" is a text value. [Urgent-Drop Everything]1 is the first cell in the column Urgent- Drop everything. Hard brackets mean you are referencing a column. 

    Your final formula should be the below, but the key to getting it is setting up the references correctly. Again, remember to name them for easier troubleshooting/upgrading down the line.

    =COUNTIFS({SVC Support Priority Priority}, "Urgent - Drop Everything", {SVC Support Priority Status}, "In Progress")

  • gwen.pino78261
    gwen.pino78261 ✭✭✭✭

    Thanks...I updated my naming convention...and selected the individual columns...It worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!