CODEWITHSUNDEEP
excel
sam
sam

Reputation: 10064

If range is blank, put an "X" in column B - excel

Ive got a spreadsheet with a matrix chart with "x" in columns d:s in column b i want to write a formula ifd:sISBLANK write "blank" in column B.

To do this i tried : =IF(ISBLANK(C2:S2) , "blank", "not blank")

But it always equates to "not blank". Any ideas where ive gone wrong ?

Ive created a sample spreadsheet for testing here : https://docs.google.com/spreadsheets/d/1b-LyPDld5a3WZue7zYSGvFbYy-tDZQLAcsRpNq4yBnU/edit#gid=0

Upvotes: 0

Views: 302

Answers (3)

fallengyro
fallengyro

Reputation: 150

You are trying to evaluate all of the cells in C2:S2 (an array) so the formula needs to be evaluated as an array formula.

Assuming that you want to display "Not Blank" if any of the cells have contents include an AND with your ISBLANK, then evaluate as an array formula.

=IF(AND(ISBLANK(C2:S2)),"Blank","Not Blank")

Try the expression with ctrl + shift + enter.

Upvotes: 1

Apurv Pawar
Apurv Pawar

Reputation: 424

try this =IF(COUNTBLANK(D2:S2)=16,"blank","")

Upvotes: 1

Laurentiu Diaconu
Laurentiu Diaconu

Reputation: 124

Try with COUNTA(range):

  • if COUNTA(range) returns something different than 0, then the range is not blank
  • else, the range is blank

Upvotes: 2

Related Questions