Help w/ COUNTIFS and ISBLANK and NOT ISBLANK
Hi team - I have 2 columns (Seat and Open Req) that I need to count:
When Seat = Filled and Open Req is not blank
When Seat = Filled and Open Req is blank
I'm struggling with the COUNTIFS sequence to get this to work.
Any help would be greatly appreciated.
Answers
-
Gil Nash ✭✭
Without knowing all of the possible options for Seat and Open Req, this may not work for you but you'll need something like this.
-
Gil Nash ✭✭
As long as the [Open Req] is always a number, this will work. There's also a ISTEXT option so you could add an OR statement to the 2nd IF statement that states OR(ISNUMBER[Open Req]@row, ISTEXT[Open Req]@row.
-
jmo ✭✭✭✭✭✭
Unfortunately the Open Req column has letters and numbers (ie: R0004321)
-
Gil Nash ✭✭
See the 2nd statement about the ISTEXT and OR function.
-
Paul Newcome ✭✭✭✭✭✭You could also try these...
[Open Req] is NOT Blank:
=条件统计(座位:座位,“填满”,[开放申请]:[重新开放q], @cell <> "")
[Open req] is Blank:
=条件统计(座位:座位,“填满”,[开放申请]:[重新开放q], @cell <> "")
Combining the two:
=条件统计(座位:座位,“填满”,[开放申请]:[重新开放q], @cell <> "") + COUNTIFS(Seat:Seat, "Filled", [Open Req]:[Open Req], @cell <> "")
OR
=条件统计(座位:座位,“填满”,[开放申请]:[重新开放q], OR(@cell <> "", @cell = ""))
-
jmo ✭✭✭✭✭✭
@Gil Nash- I tried to replicate your last formula across my specific column but got an unparseable error:
The formula I typed out was (the image has the full/correct column names:
=IF(AND(seat@row, "filled", ISBLANK([Open Position Req #]@row)), COUNTIFS(seat$1:seat@row, "filled", [Open Position Req #]$1:[Open Position Req #]@row, ISBLANK(@cell)), IF(AND(seat@row = "filled", OR(ISNUMBER([Open Position Req #]@row), ISTEXT([Open Position Req #]@row))), COUNTIFS(seat$1:seat@row, "filled", [Open Position Req #]$1:[Open Position Req #]@row, ISTEXT(@cell)), ""))
Do I have a missing parenthesis or something?
-
Gil Nash ✭✭
=IF(AND(Seat@row = "Filled", ISBLANK([Open Position Req #]@row)), COUNTIFS(Seat$1:Seat@row, "Filled", [Open Position Req #]$1:[Open Position Req #]@row, ISBLANK(@cell)), IF(AND(Seat@row = "Filled", OR(ISNUMBER([Open Position Req #]@row), ISTEXT([Open Position Req #]@row))), COUNTIFS(Seat$1:Seat@row, "Filled", [Open Position Req #]$1:[Open Position Req #]@row, ISNUMBER(@cell)) + COUNTIFS(Seat$1:Seat@row, "Filled", [Open Position Req #]$1:[Open Position Req #]@row, ISTEXT(@cell)), ""))
-
Gil Nash ✭✭
Your first IF(AND statement:
=IF(AND(seat@row, "filled"should become=IF(AND(seat@row = "filled"
The end of your formula:
ISTEXT(@cell)), ""))should becomeISNUMBER(@cell)) + COUNTIFS(Seat$1:Seat@row, "Filled", [Open Position Req #]$1:[Open Position Req #]@row, ISTEXT(@cell)), ""))
-
Gil Nash ✭✭
I just noticed in your screenshot that you are using the summary field. My formula will NOT work in a summary field.
You'll have to set up 2 fields (1 for Blank Open Position Req # and 1 for NOT Blank) and use the formulas from@Paul Newcome
NOT BLANK field:
=COUNTIFS(Seat:Seat, "Filled", [Open Position Req #]:[Open Position Req #], ISNUMBER(@cell)) + COUNTIFS(Seat:Seat, "Filled", [Open Position Req #]:[Open Position Req #], ISTEXT(@cell))
BLANK field:
=COUNTIFS(Seat:Seat, "Filled", [Open Position Req #]:[Open Position Req #], ISBLANK(@cell))
-
Paul Newcome ✭✭✭✭✭✭
I actually just realized I had a typo in one of my formulas. Here they are corrected:
[Open Req] is NOT Blank:
=条件统计(座位:座位,“填满”,[开放申请]:[重新开放q], @cell <> "")
[Open req] is Blank:
=条件统计(座位:座位,“填满”,[开放申请]:[重新开放q], @cell = "")
Using <> "" is the same as NOT(ISBLANK(@cell)) or combining the two COUNTIFS for ISTEXT(@cell) and ISNUMBER(@cell) except it saves quite a few keystrokes and gets rid of some of those pesky parenthesis.
Same for = "" replacing ISBLANK(@cell)
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
![https:\/\/us.v-cdn.net\/6031209\/uploads\/6N90SSI80H2B\/image.png<\/a><\/p>As long as the [Open Req] is always a number, this will work. There's also a ISTEXT option so you could add an OR statement to the 2nd IF statement that states OR(ISNUMBER[Open Req]@row, ISTEXT[Open Req]@row.<\/p>","bodyRaw":"[{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/6N90SSI80H2B\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/6N90SSI80H2B\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"As long as the [Open Req] is always a number, this will work. There's also a ISTEXT option so you could add an OR statement to the 2nd IF statement that states OR(ISNUMBER[Open Req]@row, ISTEXT[Open Req]@row.\\n\"}]","format":"rich","dateInserted":"2020-07-28T18:40:28+00:00","insertUser":{"userID":109731,"name":"Gil Nash","url":"https:\/\/community.smartsheet.com\/profile\/Gil%20Nash","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-11\/c2\/d3\/nc2d35c2c94967005b4b045d59d915276.jpg","dateLastActive":"2023-03-27T19:32:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/257973#Comment_257973","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/257973#Comment_257973](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/6N90SSI80H2B\/image.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/OW38SSCWWZX3\/image.png<\/a><\/p>The formula I typed out was (the image has the full\/correct column names:<\/p>=IF(AND(seat@row, "filled", ISBLANK([Open Position Req #]@row)), COUNTIFS(seat$1:seat@row, "filled", [Open Position Req #]$1:[Open Position Req #]@row, ISBLANK(@cell)), IF(AND(seat@row = "filled", OR(ISNUMBER([Open Position Req #]@row), ISTEXT([Open Position Req #]@row))), COUNTIFS(seat$1:seat@row, "filled", [Open Position Req #]$1:[Open Position Req #]@row, ISTEXT(@cell)), ""))<\/p>Do I have a missing parenthesis or something?<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Gil Nash\",\"userID\":109731}}},{\"insert\":\" - I tried to replicate your last formula across my specific column but got an unparseable error:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/OW38SSCWWZX3\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/OW38SSCWWZX3\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"The formula I typed out was (the image has the full\\\/correct column names:\\n=IF(AND(seat@row, \\\"filled\\\", ISBLANK([Open Position Req #]@row)), COUNTIFS(seat$1:seat@row, \\\"filled\\\", [Open Position Req #]$1:[Open Position Req #]@row, ISBLANK(@cell)), IF(AND(seat@row = \\\"filled\\\", OR(ISNUMBER([Open Position Req #]@row), ISTEXT([Open Position Req #]@row))), COUNTIFS(seat$1:seat@row, \\\"filled\\\", [Open Position Req #]$1:[Open Position Req #]@row, ISTEXT(@cell)), \\\"\\\"))\\n\\nDo I have a missing parenthesis or something?\\n\"}]","format":"rich","dateInserted":"2020-07-29T21:29:47+00:00","insertUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2023-01-17T16:39:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/258192#Comment_258192","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/258192#Comment_258192](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/OW38SSCWWZX3\/image.png\"){kind=link}