Formula needed
Hey
Please see attachment.
I need help with counting the number of cells in a column, (see attachment: Column [Response Sufficient?]) that contains an exclamation mark(hold) or is blank.
I basically want to count the amount of RFIs with an exclamation mark or blank cell in the [Response Sufficient?] column.
I have used the following formula, but the result is "0" which is incorrect.
=countifs([Response Sufficient?]:[Response Sufficient?],"hold",[Response Sufficient?]:[Response Sufficient?],"")
When I want to only count the rows with exclamation marks I get the correct answer
=countif([Response Sufficient?]:[Response Sufficient?],"hold")
When I want to count the blank cells, it includes the blank cells of the blank rows, which I don't want. I only want the blank cells from the complete rows.
I hope that someone can help me.
Kind regards
Jana
Comments
-
I realise now that I might have misunderstood the countifs formula. Should I perhaps count the exclamation marks and blank cells separately and then add them up in a different cell?
-
Andrée Starå ✭✭✭✭✭✭Hi Jana,
Try something like.
=COUNTIF([Response Sufficient?]:[Response Sufficient?]; "Hold") + COUNTIF([Response Sufficient?]:[Response Sufficient?]; "")
The same version but with the below changes for your and others convenience.
=COUNTIF([Response Sufficient?]:[Response Sufficient?], "Hold") + COUNTIF([Response Sufficient?]:[Response Sufficient?], "")
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work?
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Hey Andrée
Thank you for your quick reply. I have tried the formula you supplied before as well, but it still counts all the blank cells in that column for the empty rows at the bottom of the sheet as well. I only want the blank cells from the completed rows included.
I hope you understand what I am trying to say?
Best,
Jana
-
Andrée Starå ✭✭✭✭✭✭
Happy to help!
你怎么分de what is complete? Is it the Date RFI closed column?
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
The formula must only apply to rows that has a RFI nr (primary column). Does that make sense now?
-
Andrée Starå ✭✭✭✭✭✭
That makes sense!
Try this.
=COUNTIFS([Response Sufficient?]:[Response Sufficient?]; ""; [RFI Nr]:[RFI Nr]; ISNUMBER(@cell))
The same version but with the below changes for your and others convenience.
=COUNTIFS([Response Sufficient?]:[Response Sufficient?], "", [RFI Nr]:[RFI Nr], ISNUMBER(@cell))
Did it work?
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
I got it!!! Thank you so much for your help!
=COUNTIFS([Response Sufficient?]:[Response Sufficient?], "", [RFI Nr]:[RFI Nr], ISTEXT(@cell))+ COUNTIFS([Response Sufficient?]:[Response Sufficient?], "hold", [RFI Nr]:[RFI Nr], ISTEXT(@cell))- 1
The -1 in the formula is to account for the first row of the sheet. I also used ISTEXT instead of ISNUMBER because the RFI column is seen as text and not a number due to the numbers starting with a 0.
Just one question, what does (@cell)mean?
Best,
Jana
-
Andrée Starå ✭✭✭✭✭✭
Excellent!
Happy to help!
The modifications make sense.
@cellwill check each cell in the range and it's more efficient.
When you want to perform calculations in formulas that look at ranges of cells, for example: SUMIF, SUMIFS, COUNTIF, and COUNTIFS, you can use the@cellargument in the criteria of the function. The@cellargument performs a calculation on each row at the same time that the primary function (SUMIF for example) isevaluatingthe criteria in the range, making your formula more efficient.
More info:https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Jana,
I too use Smartsheet for construction and found this thread searching for RFI formulas. I have an RFI sheet that use but I feel there is room for improvement. Would love to share what I have built and discuss your approach for your sheet. Let me know if you'd like to.
Thanks,
Kurt
Help Article Resources
Categories
@SarahI<\/a> Yes you can do that just use CHILDREN([COLUMN NAME])<\/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":106901,"type":"question","name":"Can you use a COUNTIF formula to county symbols?","excerpt":"I have a list of goals for the year and we are using harvey ball symbols to indicate the percent complete an the red, yellow, green, gray balls to indicate the health\/status of the project. I would like to display a report on my dashboard that shows the number of projects we have in each harvey ball stage and also in each…","categoryID":322,"dateInserted":"2023-06-26T15:59:48+00:00","dateUpdated":"2023-06-26T16:00:12+00:00","dateLastComment":"2023-06-26T16:29:45+00:00","insertUserID":162764,"insertUser":{"userID":162764,"name":"Stephanie D","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20D","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mctWfgWrL7o!n-DX3ymVfmQ!msa32mT4_k9","dateLastActive":"2023-06-26T16:52:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162764,"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-06-26T23:23:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3375593973,"url":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106901,"commentID":382346,"name":"Re: Can you use a COUNTIF formula to county symbols?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382346#Comment_382346","dateInserted":"2023-06-26T16:29:45+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-06-26T23:23:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T16:24:48+00:00","dateAnswered":"2023-06-26T16:08:42+00:00","acceptedAnswers":[{"commentID":382328,"body":" Hi @Stephanie D<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =COUNTIF(Status:Status, \"Green\")<\/p> =COUNTIF([% Complete]:[% Complete], \"Half\")<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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"}]}],"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":[]}">
