Skip main navigation

New lower prices! Get up to 50% off 1000s of courses. 

Explore courses

Conditional Calculations

Learn more on how to use conditional calculations using average as a case study.
Conditional Calculations

Excel has functions that allow for conditional calculations. These are calculations with conditions or criteria attached.

These functions include SUMIF and AVERAGEIFS. Functions with IF and IFS attached are widely used.

The IF functions allow you to carry out the basic function, with ONE criterion attached. Whereas the IFS functions will allow you to carry out the operation with multiple conditions attached.

The table below shows the IF and IFS functions.

How can I AVERAGE with the Conditions attached?

Taking the example in the image below.

Suppose you wanted to AVERAGE the sales for blue items. Using the formula =AVERAGE(D2:D14) will result in an average for all sales.

table showing color in column B, Region in column C and Sales in column D

Therefore, we would need to use the function AVERAGEIF.

AVERAGEIF allows you to get the AVERAGE from one column based on criteria selected in another column. In our example, we want to AVERAGE column D and our criteria is in column B.

The syntax for AVERAGEIF is AVERAGEIF=(Range, Criteria, Criteria Range).

To solve this problem, we would use the formula:

=AVERAGEIF(B2:B10,”Blue”,D2:D10)

Where Range is the range you want to AVERAGE. In this case, column D.

The criteria is “Blue” (note how this is in “” as it is text).

The criteria range is the range where you will find the criteria. In this case, column B.

table showing color in column B, Region in column C and Sales in column D and formula for AverageIFS

This formula will return 33.33. It will AVERAGE the sales where the color is blue.

AVERAGEIFS will allow you to average a range of data where multiple criteria are satisfied.

The syntax for AVERAGEIFS is.

AVERAGEIFS= (AVERAGE Range, Criteria Range 1, Criteria 1, [Criteria range 2, Criteria 2]….).

The average range is the first range to select in this formula. This is the range of cells that you want to average.

Criteria Range 1 is the cells containing the first criteria. Criteria 1 allows you to specify the criteria. Second and subsequent criteria can then be added.

Suppose we wanted to Average the sales where the Colour matches that of the content in cell B12. And the Region matches that of cell C12.

table showing color in column B, Region in column C and Sales in column D a

Located in range B2:B10 is the first criteria.

Cell B12 specifies the first criteria.

Located in range C2:C10 is the second criteria. Cell C12 specifies the second criteria

table showing color in column B, Region in column C and Sales in column D and formula for AverageIFS

The created formula is =AVERAGEIFS(D2:D10,B2:B10,B12,C2:C10,C12)

This article is from the free online

Excel Skills to Make an Impression

Created by
FutureLearn - Learning For Life

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now