totn Excel

MS Excel 2003: Use an array formula to average values when 2 criteria are met

This Excel tutorial explains how to use an array formula to average values when two criteria are met in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2003/XP/2000/97, I want to average the values in column E when the corresponding value in column D is between 5 and 9.

How can I do this?

Answer: You can do this with an array formula.

Let's look at an example.

Microsoft Excel

In cell B1, we want to average the values in column E when the value in column D is between 5 and 9. To do this, we've created the following array formula that uses the SUM function:

=SUM((D2:D8>4)*(D2:D8<10)*(E2:E8))/SUM((D2:D8>4)*(D2:D8<10))

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=SUM((D2:D8>4)*(D2:D8<10)*(E2:E8))/SUM((D2:D8>4)*(D2:D8<10))}

The first part of the formula SUM((D2:D8>4)*(D2:D8<10)*(E2:E8)) will sum all of the values in column E when the corresponding value in column D is between 5 and 9.

The second part of the formula SUM((D2:D8>4)*(D2:D8<10)) will count the number of values in column D that are between 5 and 9.

When you divide the two, it gives us an average of the values in column E when the value in column D is between 5 and 9.