1

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.

2

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" )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.