Countifs

gwen.pino78261
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")

Tags:
· Share on FacebookShare on Twitter

Comments

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

    · Share on FacebookShare on Twitter
  • 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

    Countifs.JPG

    Countifs_metrics.JPG

    Countifs_SheetReferenceName.JPG

    · Share on FacebookShare on Twitter
  • 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")

    · Share on FacebookShare on Twitter
  • gwen.pino78261
    gwen.pino78261 ✭✭✭✭

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!