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

Find more posts tagged with

Construction

Formulas

Product Development

Comments

Jana@al-a

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

Jana@al-a

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!

How do you decide what is complete? Is it the Date RFI closed column?

Best,

Andrée

Jana@al-a

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

Jana@al-a

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.

@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

kurt84911

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

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…