IF Functions

IF functions

If functions are used when we want to specify a criteria to limit our function to. For instance, if we were interested in values above a certain amount we could use =countIf in order to count the number of the values in a range, that are above a certain value. There are multiple different functions that can use IF's. These include:

=SumIF

=CountIF

=averageIF

Generally anything that has a normal function can have the IF part added. All IF functions are built the same way. First we have to decide which function we want to use. You are able to use the same syntax on all of the above functions

Syntax

=AVERAGEIF(range, criteria, [average_range])

The AVERAGEIF function syntax has the following arguments:

  • Range    Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.

  • Criteria    Required. The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

  • Average_range    Optional. The actual set of cells to average. If omitted, range is used.

=COUNTIF(range,Criteria)

countIF is the most commonly used IF function, as this will count the number of values that meant the criteria you have specified. Although the other functions are built in the same way they have different purposes.

  • Range - This will be where the count is applied to, where it'll be looking for the criteria you have specified to perform a count. This can be a standard reference (A2:A20) or a table reference (Table1[ColumnHeader]) and as stated references the area that the function is going to be applied over.
  • Criteria - This is what the value we are looking for in the range will be:
    • We can either use a manual criteria written in-between speech marks ("1"). In this case the function would be looking for the value "1" inside of the range A2:A20 and will return back how many there are.
    • We can also use a reference for the criteria. Placing a value in a cell and referencing that cell will look for whatever is written in the referenced cell. 

Click here to view an animation of building this function:  Illustrative image

Referencing your criteria

You can also use a reference to cite your criteria opposed to manually placing the value in speech marks. In the above example we used a manual criteria ">=10" to state any value that's greater than or equal to 10. However if we were to type this into another cell, we can reference this instead of having it written inside the function. This means that it's easier to change at a later date if you're interested in viewing different data

Click here to view an animation showing use of referencing the criteria instead of manually adding it to the function:   Illustrative image

=AverageIF

AverageIF will take the average of the values in your range if they meet the criteria. In my example I have asked for the averaging of the score of persons who scored over 50% : Illustrative image

    =SumIF

    SumIF will total the values in the range if they meet the criteria: In my example I have asked for the Sum total of items exceeding £30:  Illustrative image

     

    Note for AverageIF & SumIF functions:

    You will also notice that AverageIF and SumIF have an optional third field when building the function: 

    • Average_range  -  Optional. The actual set of cells to average. If omitted, range is used.

    Still need help?

    Submit support request