SharePoint Online- Column Calculations Getting Total Items Missing

Question

This is for SharePoint Online

Having trouble getting column item count of empty entries. Originally I was attempting to find the total number of items missing in a column.

example:

Column A has all the data filled out but ColumnB is missing data in some rows.

I wanted to count the number of missing items missing in ColumnB.

I haven't been able to figure this out.

What I'm attempting to do now is use a calculated column to reference ColumnB and add the number 1 to another column (ColumnC). so each row that doesn't have information in ColumnB would give a numerical value of 1 to ColumnC.

This is the formula I'm attempting to use

=IF( ISBLANK([ColumnB]) , +1 , "" )

In other words

If ColumnB is empty add 1 to current column (ColumnC) else if ColumnB has data leave ColumnC empty.

I should then be able to calculate the SUM of items in ColumnC

You help would go a long way. Any suggestions would be greatly appreciated.

Answer 1

SharePoint Views do not support Total/Sum/Avg type operations on a Calculated Column. (In SharePoint 2007 and 2010 we could use SharePoint Designer to "hack" this.)

Three workarounds...

1. Create a workflow that does the test and then saves a 0 or 1 to a Number column. You can then sum that column in the View.
2. Create a View that groups on the Calculated Column. The groups will include counts. "Column C : 1 (8)" and "Column C : 0 (20)".
3. Add JavaScript to the page to extract and count the Calculated Column data.

The preferred way to write the formula is to have all numeric or all text data:

=IF( ISBLANK([ColumnB]) , 1 , 0 )

or

=IF( ISBLANK([ColumnB]) , "missing data" , "complete" )