Count Blank Cells

I am using Smartsheet for invoice approvals and would like to summarise how many documents are still awaiting approval.

I have tried using a countif formula to count all cells that are blank in a certain column, however I get results that include lines are not used, e.g. there are 10 documents entered on separate lines however there are another 40 completely blank lines and I get the result of 45 blank lines (40 blank lines and 5 that aren't approved). I just want the 5 counted.

(Those numbers are just examples, there will be more than 10 on the sheet)

Is there a workaround for this?

Thanks,

Emma

Screen Shot 2017-04-13 at 21.07.35.png

Screen Shot 2017-04-13 at 21.12.55.png

Screen Shot 2017-04-13 at 21.15.09.png

Screen Shot 2017-04-13 at 21.07.35.png

Screen Shot 2017-04-13 at 21.12.55.png

Screen Shot 2017-04-13 at 21.15.09.png

Screen Shot 2017-04-13 at 21.07.35.png

Screen Shot 2017-04-13 at 21.12.55.png

Screen Shot 2017-04-13 at 21.15.09.png

Screen Shot 2017-04-13 at 21.07.35.png

Screen Shot 2017-04-13 at 21.12.55.png

Screen Shot 2017-04-13 at 21.15.09.png

Find more posts tagged with

Manufacturing

Finance

Formulas

Comments

AJ_vf

I would make all yellow rows children of row 1. Then you can use this:

=COUNT(CHILDREN($[PIR Number]$1)) - COUNT(CHILDREN($[Approval 1]$1))

John Creason

Emma -

See if this gives you what you're after:

=COUNT([PIR Number]:[PIR Number]) - COUNT([Approval 1]:[Approval 1])

The COUNT function will count cells which are not blank, so this should give you the right answer.

Hope this helps,

John

John Creason

Your solution works even without the hierarchy changes... COUNT will only include the non-blank rows.