Formula countifs and is blank

I am trying to count the number of rows that contain the purpose of "appointment help" only not how many cells contain "appointment help". Trying to determine how many calls received were the result of needing help scheduling an appointment only.

I currently have 311, using, =COUNTIF([Purpose 1]:[Purpose 5], CONTAINS("Appointment", @cell)) and have tried the following but continue to get an error:

=COUNTIFS([Purpose 1]:[Purpose 1], HAS("appointment help", @cell), AND(ISBLANK([Purpose 2]:[Purpose 5], @cell)))

=COUNTIFS([Purpose 1]:[Purpose 1], CONTAINS("appointment help", @cell), AND(ISBLANK([Purpose 2]:[Purpose 5], @cell)))

=COUNTIFS([Purpose 1]:[Purpose 1], HAS("Appointment Help", @cell), [Purpose 2]:[Purpose 5], AND(ISBLANK("", @cell)))

=COUNTIFS([Purpose 1]:[Purpose 1], CONTAINS("Appointment Help", @cell), [Purpose 2]:[Purpose 5], AND(ISBLANK("", @cell)))

Answers

  • blmccue
    blmccue ✭✭

    Screenshot if needed. I can instantly see in the image below that there are 6 calls identified as "Appointment Help"; however, 2 of them also include a secondary purpose so I want to exclude those from this count.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You don't need the AND function in a COUNTIFS. It is already implied. It also looks like your first range is a single column and the second range is multiple columns?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • blmccue
    blmccue ✭✭

    Sorry Paul, poor copy and paste on my part. I couldn't get it to recognize the range so I change it to one column and forgot to change it back.

    I've changed it to this now and still get an incorrect argument set error.

    =COUNTIFS([Purpose 1]:[Purpose 5], CONTAINS("Appointment Help", @cell), [Purpose 2]:[Purpose 5], ISBLANK("", @cell))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The first section of the ISBLANK function needs removed. It should just be ISBLANK(@cell).


    It also still looks like your ranges are two different sizes. The first is 5 columns and the second is 4 columns. Is that accurate?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • blmccue
    blmccue ✭✭

    I am not sure if it is accurate. I can't see a person calling our office having more than 5 reasons for calling. I just assumed that the first column would most likely have the matching criteria that I am looking for and I would only count the subsequent columns if they are blank since I am only looking for the ones that are identified as appointment help.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I missed your initial screenshot. I wish I had seen that sooner.


    If there is only one purpose, will it ALWAYS be in the first column, or could it go more like

    blank | blank | purpose | blank | blank

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • blmccue
    blmccue ✭✭

    Hi Paul, thanks for your help. There could be more than one purpose which is why they are divided into the 5 different columns but I would like to be able to count the ones that only called with the need for help scheduling an appointment thus the other purposes would be blank.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I understand that. What I am asking though is if I only enter 1 purpose, would it always be in the first column or could the first 2 columns be blank, my purpose is in the 3rd column, and the 4th and 5th columns are blank?


    Which column would my entry be in if I only had one purpose?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • blmccue
    blmccue ✭✭
    edited 06/13/23

    Yes if there is only one purpose it will always be in the first column with the 2nd purpose showing in the 2nd column, etc.

    Staff selects from the following and before the data is imported into Smartsheet it is separated into separate columns.

    It isn't very common to see a 4th or 5th reason but not unheard of.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So really you need to count when the first column is "appointment help" and the rest are blank. In that case you would use something more along the lines of...

    =COUNTIFS([Purpose 1]:[Purpose 1], @cell = "Appointment Help", [Purpose 2]:[Purpose 2], @cell = "", [Purpose 3]:[Purpose 3], ...................................)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • blmccue
    blmccue ✭✭

    Got it, so I really needed to break each column down instead of trying to group them all together. Thank you

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!