Skip main navigation

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.

table showing functions and descriptions

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

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education