Excel: How to Calculate Average If Cell Contains Number


You can use the following formula to calculate the average in Excel only for the cells that contain a number in a corresponding range:

=SUMPRODUCT(--ISNUMBER(A2:A11), B2:B11) / COUNT(A2:A11)

This particular formula will calculate the average of the values in the range B2:B11 only for the cells that contain a number in the range A2:A11.

The following example shows how to use this formula in practice.

Example: Calculate Average If Cell Contains Number

Suppose we have the following dataset in Excel that shows the number of sales made by employees at a company with certain ID’s:

We can use the following formula to calculate the average sales made by only the employees who have a numeric value for ID:

=SUMPRODUCT(--ISNUMBER(A2:A11), B2:B11) / COUNT(A2:A11)

The following screenshot shows how to use this formula in practice:

The average sales among employees with a numeric ID is 12.

We can manually verify that this is correct by identifying which employees have numeric ID’s:

The average sales made by these employees can be calculated as:

Average Sales = (19 + 4 + 8 +17) / 4 = 12.

This matches the value calculated using our formula.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Calculate Average If Between Two Values in Excel
How to Calculate a Cumulative Average in Excel
How to Find Weighted Moving Averages in Excel

Leave a Reply

Your email address will not be published.