using if statement

%3CLINGO-SUB%20id%3D%22lingo-sub-1747391%22%20slang%3D%22en-US%22%3Eusing%20if%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1747391%22%20slang%3D%22en-US%22%3E%3CP%3E%3CBR%20%2F%3EI%20want%20to%20check%20if%20an%20entire%20column%20not%20just%20one%20cell%3D%20415%20(the%20number)%3CBR%20%2F%3EHow%20to%20do%3F%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EIF(K3%3AK18%3D%E2%80%9D415%E2%80%9D%2C-1%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BJ%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20K%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20L%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20M%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20N%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BO%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BP%3C%2FP%3E%3CTABLE%20width%3D%22469%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E-1%3C%2FTD%3E%3CTD%20width%3D%2267%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2267%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2267%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2267%22%3ED%3C%2FTD%3E%3CTD%20width%3D%2267%22%3EE%3C%2FTD%3E%3CTD%20width%3D%2267%22%3Ey%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E415%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E25%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E1.28%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E63%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E550%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E25%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E4.05%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E415%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E95%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E4.05%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E36%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E550%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E95%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E1.28%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E99%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E415%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E25%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E4.05%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E24%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E550%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E25%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E1.28%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E66%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E415%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E95%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E1.28%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E71%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E550%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E95%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E4.05%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E54%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E9%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E415%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E25%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E60%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E4.05%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E10%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E550%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E25%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E60%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E1.28%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E74%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E11%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E415%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E95%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E60%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E1.28%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E80%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E12%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E550%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E95%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E60%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E4.05%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E33%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E13%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E415%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E25%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E60%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E1.28%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E63%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E14%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E550%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E25%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E60%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E4.05%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E415%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E95%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E60%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E4.05%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E44%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2267%22%3E16%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E550%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E95%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E60%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E1.28%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E96%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%20MM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1747391%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1747456%22%20slang%3D%22en-US%22%3ERe%3A%20using%20if%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1747456%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F821111%22%20target%3D%22_blank%22%3E%40mmarion%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(COUNTIF(K3%3AK18%2C%22%26lt%3B%26gt%3B415%22)%3D0%2C-1%2C1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1749386%22%20slang%3D%22en-US%22%3ERe%3A%20using%20if%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749386%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%26nbsp%3B%3C%2FSPAN%3E%3CEM%3ECOUNTIF%3C%2FEM%3E%3CSPAN%3E%26nbsp%3Bfunction%20in%26nbsp%3B%3C%2FSPAN%3E%3CEM%3EExcel%3C%2FEM%3E%3CSPAN%3E%26nbsp%3Bcounts%20the%20number%20of%20cells%20that%20meet%20criteria%20you%20specify.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20finally%20just%20did%20an%20overall%20search%20and%20replace%20column%20by%20column.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIt%20would%20be%20nice%20to%20automate%20that.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20sure%20what%20is%20supposed%20to%20be%20before%20'%26nbsp%3B%3CA%20href%3D%22mailto%3A.%40Hans%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E.%40Hans%3C%2FA%3E%26nbsp%3BVogelaar'%20in%20this%20reply.%20I%20hope%20this%20post%20is%20readable.%3C%2FP%3E%3CP%3E%3CSPAN%3EMM%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor


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

-1ABCDEy
1415255401.2863
2550255404.0521
3415955404.0536
4550955401.2899
54152515404.0524
65502515401.2866
74159515401.2871
85509515404.0554
9415255604.0523
10550255601.2874
11415955601.2880
12550955604.0533
134152515601.2863
145502515604.0521
154159515604.0544
165509515601.2896

 

Thanks. MM

3 Replies
Highlighted

@mmarion 

Use

 

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

Highlighted

@Hans Vogelaar 

 

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

@mmarion 

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.