Formula needed

Jana@al-a

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

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