Search form

How to use the IF function in Excel to calculate values based on different criteria

The IF statement is a simple function in Excel that is one of the building blocks you need when you are working with large spreadsheets. You may not know you need it yet, but once you know how to use it, you won't want to live without it.

IF() Function Syntax

The IF() function has the following syntax:

=IF(logical test, value if true, value if false)

The IF function works by performing a logical test that can only have one of two outcomes - TRUE or FALSE. It then outputs a result based on the outcome of that test.

Logical Tests in Excel

You can use anything as a logical test provided Excel can determine whether the outcome is TRUE or FALSE.

Some examples of logical tests that you can use with the IF function include:

  • C5=C6 (compare cell C5 to C6. If they are equal, the outcome is TRUE, otherwise the outcome is FALSE).
  • C5>C6 (if C5 is greater then TRUE, otherwise FALSE)
  • C5="" (if cell C5 is empty then TRUE, otherwise FALSE)
  • SUM(A5:A10)>B5 (if the sum of cells A5 to A10 is greater than B5 then TRUE, otherwise FALSE).

There are a number of ways to construct more complex logical tests which we won't cover here.

Examples of the IF() function in action

To use the IF() function, follow these steps:

  • Click on a cell and enter the IF() function:
    Typing the IF function in Excel
  • Enter the logical test as shown in this example:
    Worked example of the IF statement
  • The value in the Result column is the outcome of the IF formula
    • The logical test checks to see whether the cell in the Day column (B5) = "Wednesday" (we use the speech marks to tell Excel the value we're performing the test on is text rather than a number)
    • If the value in the Day cell is "Wednesday", then the result will be Yes.
    • Otherwise the result will be No, as it is in this case.

There are many ways to use the IF() function in Excel that we won't cover here, but look out for more advanced IF() lessons coming soon.

Want to learn more? Try these lessons:

Our Comment Policy.

We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.

Add a comment to this lesson

Comments on this lesson

Adding cells that must be blank when no data present.

Submitted by on

Hi, How do you add a formula to a cell that will only give a result when data is present, but will be blank when no data has been entered.
Then add more of these formatted cells together and again only show a result when some data has been entered, but remain blank when no data entered. I keep getting #value!

Blank Cells referenced in my IF statement.

Submitted by on

My IF statement is comparing the text value in two cells but I want the return to be blank if one of the cells is blank.
=IF(L2=N2, "Valid", "Variance")
However, if either L2 or N2 is empty, I don't want anything as the result.

Hello

Submitted by on

=IF(L2="",,IF(N2="",,IF(L2=N2, "Valid","Variance")))

It's called a nested conditional. There used to be a limit on how many levels a nest can go. I'm not sure if the limit is still there and how deep you can go.

IF THEN statements

Submitted by on

IF(H16=2,1.5,IF(H16=1,1)
IF(H18=2 I need it to factor in 1.5-.5 how do I do that

Using IF statement in Excel

Submitted by on

If we use more than two cells to compare the value (Eg. "C5=C6=C7") den the result generated is false. Please suggest me the correct method.

Can't Figure out error

Submitted by on

I am working on a spreadsheet that requires multiple IF statements. It was working great until I had to add in 1 more variable and now there is an error and I cannot figure out what it is. Can someone help me out? Below is the formula:
=if(o12="m",(round(g12*01),2)),if(o12="y",(if(12=0,0,round(if(and(C$5,c$6,G12>f12),f12*.04,if(and(c$5.c$6,g12>f12),f12*.0275,f12*.01)),2),"NA")

I have also attached a copy

John

Submitted by on

mometasone spray over counter efeefafdbffegefb

if FALSE calculate

Submitted by on

This is wrong - and I know it... (I keep dragging my brain back to BASIC - yeah, I'm that old)

=IF(N3,0,"=D3*.0025")

the first half is right - if there is something in that field I do want this field to be 0.
However, if there is nothing in the first field I want excel to calculate D3*.0025...
and running down the sheet, D4*.0025; D5*.0025; etc...

Does anyone know the formula that will run the calculation?

I hope you have resolved this

Submitted by on

I hope you have resolved this by now, but for anyone who was googling to solve this exact issues, I discovered that you need to remove the quotations to get it register the cell recall
=IF(N3,0,"=D3*.0025")

becomes
=IF(N3,0,=D3*.0025)
the comma segments the formula, when using the quotation marks for the formula it will as a string of text between the quotes
"=D3*.0025"
returns as the text: "=D3*.0025

Add comment