+ Start a Discussion
Jacek DJacek D 

Formula help - Counting empty fields

Hi,

 

I am trying to build a formula that counts empty fields. All fields are numerical. Users may populate them all, but these are not required. So I'd like to count how many fields they did not populate. Here's the number formula I built -- it simply returns 0. Any ideas?

 

IF  ( ISBLANK ( accceptance_assign_to_self_time__c  ) , 1 , 0 ) + 
IF  ( ISBLANK ( response_time__c ) , 1 , 0 ) + 
IF  ( ISBLANK ( clarity__c ) , 1 , 0 ) + 
IF  ( ISBLANK ( grammar__c ) , 1 , 0 ) + 
IF  ( ISBLANK ( spelling__c )  , 1 , 0 ) + 
IF  ( ISBLANK ( punctuation__c ) , 1 , 0 ) + 
IF  ( ISBLANK ( tone__c )  , 1 , 0 ) + 
IF  ( ISBLANK ( accuracy_of_refund_request__c ) , 1 , 0 ) + 
IF  ( ISBLANK ( answered_customer_correctly__c ) , 1 , 0 ) + 
IF  ( ISBLANK ( appropriate_changes_to_account__c ) , 1 , 0 ) + 
IF  ( ISBLANK ( case_categorization__c )  , 1 , 0 ) + 
IF  ( ISBLANK ( escalations__c ) , 1 , 0 ) + 
IF  ( ISBLANK ( follow_up__c ) , 1 , 0 ) + 
IF  ( ISBLANK ( customer_satisfaction__c ) , 1 , 0 ) + 
IF  ( ISBLANK ( follow_procedures_and_guidelines__c) , 1 , 0 )

 

Thanks!

Best Answer chosen by Admin (Salesforce Developers) 
SteveMo__cSteveMo__c

Do the fields have a default value?  Try using <= 0 instead of ISBLANK to see if you get different results.

All Answers

SteveMo__cSteveMo__c

Do the fields have a default value?  Try using <= 0 instead of ISBLANK to see if you get different results.

This was selected as the best answer
Jacek DJacek D

There's no dafult value, therefore the ISBLANK. I'd like to avoid having a number in there if not used.

SteveMo__cSteveMo__c

I'm just trying to rule things in or out at this point.

Jacek DJacek D

But hey! <=0 worked!

 

Thanks!

SteveMo__cSteveMo__c

after all we've been through together, how could you ever doubt me???

Jacek DJacek D

I don't doubt! I just have delays in meaningful responses.

 

Thanks again :)

SteveMo__cSteveMo__c

PS.  You owe me a beer!  

and I drink these -> http://beeradvocate.com/beer/profile/863/7971

Eric Boller 14Eric Boller 14
I actually had a simliar issue recently when trying to use ISBLANK and ISNULL to compute an average in a formula field only using fields that were populated. Even when a field was null, the formula acted like it wasn't. However, using <0 made the formula recognize when the fields were blank, and not count them in the average. I wonder why less than 0 would work but the ISBLANK/ISNULL functions wouldn't.