CODEWITHSUNDEEP
excelif-statementexcel-formula
Josh
Josh

Reputation: 107

How can I ignore or address blank cells in an Excel function?

I have 7 columns and I want to check if each value in the columns are the same. However, each column does not have a value. I would like to create a function that says:

=IF("A = B = C = D = F = I = J", Value of the cells, False) However, I want to ignore any blank cells, so it does not trigger a False.

enter image description here

The actual ID column should populate to: False, 2, 3, 4, 5, 6, 7.

Upvotes: 0

Views: 146

Answers (2)

tnavidi
tnavidi

Reputation: 1393

You can use this formula, will work for texts as well:

=IF(COUNTA(A11:G11)=COUNTIF(A11:G11,LEFT(TEXTJOIN("",TRUE,A11:G11);LEN(TEXTJOIN("",TRUE,A11:G11))/COUNTA(A11:G11))),LEFT(TEXTJOIN("",TRUE,A11:G11),LEN(TEXTJOIN("",TRUE,A11:G11))/COUNTA(A11:G11)))

Upvotes: 0

Josh
Josh

Reputation: 107

I got an answer. I am using =if(max(range) = min(range), max(range), FALSE)

This works!

Upvotes: 2

Related Questions