Count range of cells whether they are blank or have text
I am trying to count a few things in this sheet to track % of the Cabin completion. I want the total # of cells in a row that are either blank OR have text in them so that I know when all the cells in that row have been completed. I have tried multiple COUNTIF scenarios and its not working.
completed.
Best Answer
-
Amber Eakin ✭✭✭✭✭✭
Hi @JennS - I have an idea! Let me know if this helps!
=COUNTIF({RANGE}, OR(@cell = "", @cell <> ""))Amber Eakin
Instructional Librarian
Answers
-
Amber Eakin ✭✭✭✭✭✭
Hi @JennS - I have an idea! Let me know if this helps!
=COUNTIF({RANGE}, OR(@cell = "", @cell <> ""))Amber Eakin
Instructional Librarian
-
JennS_ ✭✭
@Amber EakinIt worked! Thank you so much for your quick help!
-
Marilen.Navarro103391 ✭✭✭✭✭如果我想每月计算“空白”吗
=COUNTIFS({Battery CE Leads Range 1}, "Unqualified", {Battery CE Leads Range 2}, OR(@cell = "", {Battery CE Leads Range 3}, AND(IFERROR(MONTH(@cell), 0), Apr$1, IFERROR(YEAR(@cell), 0), =2023)))
Help Article Resources
Categories
Check out theFormula Handbook template!
=IF(COUNTIFS(CHILDREN(), OR(@cell = \"Not Started\", @cell = \"\")) = COUNTIFS(CHILDREN(), OR(@cell = \"\", @cell <> \"\")), \"Not Started\", IF(COUNTIFS(CHILDREN(), @cell = \"Complete\") = COUNTIFS(CHILDREN(), OR(@cell = \"\", @cell <> \"\")), \"Complete\", IF(COUNTIFS(CHILDREN(), @cell = \"On Hold\") = COUNTIFS(CHILDREN(), OR(@cell = \"\", @cell <> \"\")), \"On Hold\", IF(COUNTIFS(CHILDREN(), @cell = \"Cancelled\") = COUNTIFS(CHILDREN(), OR(@cell = \"\", @cell <> \"\")), \"Cancelled\", \"In Progress\"))))<\/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":107380,"type":"question","name":"COUNTIFS not working where one reference is a column with multiple drop downs...","excerpt":"=COUNTIFS({New RT - Priority}, [Column2]$28, {New RT - Platform}, \"CASI\") This isn't working... I want a count for if the Priority is Critical (this is in [Column2]$28) and the Platform (from a drop down column) includes CASI. (There may be multiple entries in the drop down column). I know the answer is 1 but it's not…","snippet":"=COUNTIFS({New RT - Priority}, [Column2]$28, {New RT - Platform}, \"CASI\") This isn't working... I want a count for if the Priority is Critical (this is in [Column2]$28) and the…","categoryID":322,"dateInserted":"2023-07-10T13:02:35+00:00","dateUpdated":null,"dateLastComment":"2023-07-10T14:33:19+00:00","insertUserID":163269,"insertUser":{"userID":163269,"name":"nualamcd","title":"mrs","url":"https:\/\/community.smartsheet.com\/profile\/nualamcd","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!0uv0_y0teLM!uuO0JyLUlnk!LZ3wQE_hh99","dateLastActive":"2023-07-10T17:02:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-10T20:09:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":32,"score":null,"hot":3377995554,"url":"https:\/\/community.smartsheet.com\/discussion\/107380\/countifs-not-working-where-one-reference-is-a-column-with-multiple-drop-downs","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107380\/countifs-not-working-where-one-reference-is-a-column-with-multiple-drop-downs","format":"Rich","lastPost":{"discussionID":107380,"commentID":384361,"name":"Re: COUNTIFS not working where one reference is a column with multiple drop downs...","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/384361#Comment_384361","dateInserted":"2023-07-10T14:33:19+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-10T20:09:43+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-07-10T13:13:20+00:00","dateAnswered":"2023-07-10T13:06:27+00:00","acceptedAnswers":[{"commentID":384336,"body":"
If you are using a multi-select dropdown, you will want to include a HAS function like so:<\/p>
=COUNTIFS({New RT - Priority}, [Column2]$28, {New RT - Platform}, HAS(@cell, \"CASI\")<\/strong>)<\/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":107379,"type":"question","name":"How to find duplicates in the document name column?","excerpt":"There are more than 700 documents and I want to find duplicate rows, I am new to Smartsheet.","snippet":"There are more than 700 documents and I want to find duplicate rows, I am new to Smartsheet.","categoryID":322,"dateInserted":"2023-07-10T12:57:20+00:00","dateUpdated":"2023-07-10T13:23:10+00:00","dateLastComment":"2023-07-10T13:05:27+00:00","insertUserID":163271,"insertUser":{"userID":163271,"name":"Umar Farooq","title":"MR","url":"https:\/\/community.smartsheet.com\/profile\/Umar%20Farooq","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-10T13:22:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":163271,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-10T20:09:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":19,"score":null,"hot":3377988767,"url":"https:\/\/community.smartsheet.com\/discussion\/107379\/how-to-find-duplicates-in-the-document-name-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107379\/how-to-find-duplicates-in-the-document-name-column","format":"Rich","lastPost":{"discussionID":107379,"commentID":384335,"name":"Re: How to find duplicates in the document name column?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/384335#Comment_384335","dateInserted":"2023-07-10T13:05:27+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-10T20:09:43+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-07-10T13:23:23+00:00","dateAnswered":"2023-07-10T13:05:27+00:00","acceptedAnswers":[{"commentID":384335,"body":" Try this in a checkbox or flag type column:<\/p> =IF(COUNTIFS([Document Name]:[Document Name], @cell = [Document Name]@row)> 1, 1)<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
