IF practice in Google Sheets
IF works the same way in Google Sheets as in Excel: IF(condition, value_if_true, value_if_false). Text results go in quotes, and IFs can be nested inside each other.
How IF works
IF makes a formula choose between two answers. It takes three arguments: a test that is either true or false, what to show when it is true, and what to show when it is false.
=IF(B2>=50,"Pass","Fail") shows Pass when the score in B2 is 50 or more and Fail otherwise. Text answers go in quotes; numbers and formulas don't, so =IF(C2>1000,C2*0.05,0) works out a 5% bonus only on sales over 1,000.
For more than two outcomes you can put one IF inside another, but past two or three levels IFS or a lookup table is easier to read. To test two conditions at once, put AND or OR inside the test. The exercises below cover pass or fail, a calculated bonus, comparing two cells, checking for text or an empty cell, and a nested IF.
- 1Pass or fail with IFThe passing score is 50. In C2, use IF to show "Pass" when the score in B2 is 50 or more, and "Fail" otherwise.
- 2Calculate a bonus with IFPremiumSellers with sales over 1000 get a 10% bonus; everyone else gets 0. In C2, use IF to calculate the bonus for B2.
- 3Three outcomes with nested IFPremiumGrade the score in B2: 90 or more is "A", 70 or more is "B", anything lower is "C". In C2, put a second IF inside the first.
- 4Flag missing values with ISBLANKPremiumIn D2, show "Missing" when the hours in B2 are blank, and otherwise the pay, B2*C2. Use IF with ISBLANK.
- 5Compare two cells with IFPremiumIn D2, show "Over budget" if the amount spent in C2 is more than the budget in B2, and "OK" if not.
- 6IF a cell contains a wordPremiumIn B2, show "Pro" if the product name in A2 contains "pro" anywhere, and "Standard" if it does not. IF has no wildcards, so test with ISNUMBER(SEARCH("pro",A2)).
Functions in this topic
Questions
Is IF in Google Sheets the same as in Excel?
Yes. It takes the same three arguments, and conditions with =, <>, <, >, AND and OR are written the same way.
How do I use IF on a whole column in Google Sheets?
Wrap it in ARRAYFORMULA and use an open range: =ARRAYFORMULA(IF(B2:B>=50,"Pass","Fail")). One formula then fills the whole column, and new rows are included. Empty cells count as 0 and show Fail, so test for them first with IF(B2:B="","",...).