I need to return a given value if something is true! (IF statement)

We can do this in excel with a single formula:

  • IF: based upon a given criteria return one value if true and another if false

Syntax

=IF(my_condition, value_if_true, value_if_false)

Example

Let’s use an example to illustrate the formula. We want to return only colours where our number is greater than 2 (the black cells).

The Steps – If statement

my_condition: Is the number greater than 2? (logical_test)

=IF(number > 2

value_if_true: the colour (value_if_true)

=IF(number > 2, give me the colour

value_if_false: 0 (value_if_false)

=IF(number > 2, give me the colour, give me 0)

Our result: 1 is greater than 2? FALSE. Therefore we get 0.

Fill down: apply this to each row by dragging the green fill handle at the bottom corner of the cell that contains our formula.

Result – if statement

formula

=IF(B3>2,D3,0) → 1>2 FALSE → 0
=IF(B4>2,D4,0) → 2>2 FALSE → 0
=IF(B5>2,D5,0) → 3>2 TRUE → Blue
=IF(B6>2,D6,0) → 4>2 TRUE → Green
=IF(B7>2,D7,0) → 5>2 TRUE → Purple

output

And there you have it, colours where the value is greater than 2!

Need to sum values if a condition is true? Use Sumif!