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?
-
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
Happy to help!
How do you decide 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
The formula must only apply to rows that has a RFI nr (primary column). Does that make sense now?
-
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
Excellent!
Happy to help!
The modifications make sense.
@cell will 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 @cell argument in the criteria of the function. The @cell argument performs a calculation on each row at the same time that the primary function (SUMIF for example) is evaluating the 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!