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?
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!
-
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?
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!
-
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
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!
-
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?
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!
-
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], ...................................)
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!
-
Got it, so I really needed to break each column down instead of trying to group them all together. Thank you
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!