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
-
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.
-
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?
-
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))
-
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?
-
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.
-
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
-
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.
-
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?
-
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.
-
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], ...................................)
-
Got it, so I really needed to break each column down instead of trying to group them all together. Thank you
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!