Highlighted
New Contributor

using if statement

I want to check if an entire column not just one cell= 415 (the number)
How to do?

Example:

IF(K3:K18=”415”,-1,1)

Data:

J            K              L              M            N             O         P

 -1 A B C D E y 1 415 25 5 40 1.28 63 2 550 25 5 40 4.05 21 3 415 95 5 40 4.05 36 4 550 95 5 40 1.28 99 5 415 25 15 40 4.05 24 6 550 25 15 40 1.28 66 7 415 95 15 40 1.28 71 8 550 95 15 40 4.05 54 9 415 25 5 60 4.05 23 10 550 25 5 60 1.28 74 11 415 95 5 60 1.28 80 12 550 95 5 60 4.05 33 13 415 25 15 60 1.28 63 14 550 25 15 60 4.05 21 15 415 95 15 60 4.05 44 16 550 95 15 60 1.28 96

Thanks. MM

3 Replies
Highlighted

Re: using if statement

Use

=IF(COUNTIF(K3:K18,"<>415")=0,-1,1)

Highlighted

Re: using if statement

The COUNTIF function in Excel counts the number of cells that meet criteria you specify.

I finally just did an overall search and replace column by column.

It would be nice to automate that.

I am not sure what is supposed to be before ' .@Hans Vogelaar' in this reply. I hope this post is readable.

MM

Highlighted

Re: using if statement

The forum automatically starts a reply with @username where username is the one you're replying to. There isn't supposed to be anything before it.