Formula countifs and is blank

blmccue

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)))

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

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?

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

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?

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

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

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

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?

blmccue

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

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], ...................................)

blmccue

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

Paul Newcome

Happy to help. 👍️