CODEWITHSUNDEEP
excel-formula
Janki_19
Janki_19

Reputation: 47

Formula to check if all non-empty columns have a particular value

Like in title: I need to check if the columns have a value='bob' if they are not empty. If all columns have the value 'bob' then return true.

 |  A  |  B  |  C  |  D  |
1| bob | bob |     |     | - should return true
2| bob |     | bob | bob | - should return true
3| bob | tom |     | bob | - should return false

Can anyone help me with the formula for this?

Upvotes: 1

Views: 56

Answers (2)

Terry W
Terry W

Reputation: 3257

This is my try:

In Cell E1, enter the following formula and drag it down.

=(COLUMNS(A1:D1)-COUNTIFS(A1:D1,"bob")=COUNTBLANK(A1:D1))

Solution

Upvotes: 1

EDS
EDS

Reputation: 2195

With the newest version of Excel, try: =IF(CONCAT(UNIQUE($A1:$D1,1))="Bob", TRUE)

EDIT: As pointed out by Terry W, this won't work in cases where non-blank cells sequentially contain "Bob", so a more accurate approach might instead be

=IFERROR(IF(COUNTIF($A1:$D1, "Bob")/COUNTA($A1:$D1)=1, TRUE), FALSE)

Upvotes: 0

Related Questions