Formula to show which fields are blank

Ask the community Formulas
#1

Hi there,

Lets say I have four fields:

A
B
C
D

I would like to create a single formula field that could show which of the above fields are blank.

So, for example:

If only A is blank, the field should say “Field A is blank”
If A and B are blank, the field should say “Field A and B are blank”

And so on …

I have been able to create a formula that tracks whether all the fields are blank, but I am struggling to find a way to use the formula field to display which specific fields are blank.

If anyone has any advice, that would be much much appreciated!

#2

Having a formula that produces a natural language sentence requires a lot of complex logic that is hard to maintain. It is much easier to create an independent sentence for each item.

CONCATENATE(
    IF( NOT({A} & ""), "A is blank\n"),
    IF( NOT({B} & ""), "B is blank\n"),
    IF( NOT({C} & ""), "C is blank\n"),
    IF( NOT({D} & ""), "D is blank\n")
)
4 Likes
#3

Thank you so much, this is so useful.

Can I ask, if I wanted to create a formula that would count how many blanks are blank, how would I go about doing that? Is there a way to insert a counter into a formula that would increment each time specific criteria are met (e.g., in this case, that the field is blank)?

I really appreciate your help!

#4

You can add up the number of blanks. Use the same method to tell if a field is blank.

SUM(
    IF( NOT({A} & ""), 1),
    IF( NOT({B} & ""), 1),
    IF( NOT({C} & ""), 1),
    IF( NOT({D} & ""), 1)
)
1 Like
#5

Thanks again, this has been so useful!

#6

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.