Excel for Office & Home

IF Functions - working with Logical Functions
Overview
=IF()
Nested IF Statements
=AND()
=OR()
=COUNTIF()
=COUNTIFS()
=SUMIF()
=SUMIFS()

Overview When analysing data in Excel, we often want to pick some data and eliminate unwanted data
(temporarily or permanently). This can be done in a number of ways:
● Using the functions =IF() | =AND() | =OR() | =COUNTIF()
● Using filters
● Using PivotTables
For the purpose of this topic we are going to concentrate on the functions.

=IF()
The =IF() function is used to highlight one set of data over another.
The format of the function is:
● IF some condition / criteria (logical test) is TRUE, then return this value, otherwise return that value For example if cell A2 contained 1, and cell B2 contained 6; we could set up an if statement to detect if consecutive cells contained the same value.
● In cell C2 we could enter =IF(A2=B2,)
● In English this reads "If the value in cell A2 equals the value in cell B2, then return 0 otherwise return 'FALSE'"

1

Excel for Office & Home

● Alternatively we could enter =IF(A2=B2,1,0)
● In English this reads "If the value in cell A2 equals the value in cell B2, then return 1 otherwise return 0"
● This is more useful because we could then use the =SUM() function to total the amount of scenarios where this condition is true.

Nested IF Statements
The =IF() function can be used to compare more than two scenarios. Let's say you wanted to create a RAG (Red, Amber, Green) report which is based on the following criteria:
1. If the due date is more than a month ahead ­ Status Green
2. If the due date is within a month ­ Status Amber
3. If the due date is passed ­ Status Red If Column A contains the due date and column B contains the formula for the status. We can utilise another function called =Today() which reads the current date from the computer.
Therefore, the three criteria are:
1. If the date in A2 is less than today then the status is Red
2. If the date in A2 is more than month ahead of today then the status is Green
3. Otherwise the status must be Amber To put this in an =IF() statement:

2

Excel for Office & Home
=IF(A2<TODAY(),"RED",IF(A2>TODAY()+30,"GREEN","AMBER"))

There are several things to note here:
1. The "=" sign is only used once at the beginning of the formula.
2. The =IF() function can hold the following outcomes:
● No values ­ then returns 0 or FALSE
● One value ­ then returns that value or FALSE
● Two values ­ then returns the first value if true or the second value if false
● One value and another IF() statement ­ returns the first value if true or evaluates the second IF() statement If you wanted you could add conditional formatting to add some more polish to this report.

=AND()
The =AND() function is used when both or all conditions are true. This function has limited use because it only returns TRUE or FALSE. It will return TRUE only if all conditions within the function are true otherwise it will return FALSE.

=OR()
The =OR() function is used when any conditions are true. This function has limited use because it only returns TRUE or FALSE. It will return TRUE if any condition within the function is true otherwise it will return FALSE if they are all false.

=COUNTIF()
The =COUNTIF() function is useful because it will only count elements where they are equal to a certain value.

3

Excel for Office & Home

In the scenario above we have a list of almost 300 marital status'. The =COUNTIF() function has been used in cell B2 to only count if the value in column A is equal to "S", in C2 it only counts if the value is "M". In cell D2 the number of blanks have been