AVERAGEIF calculation across two ranges, you must nest the function inside a SUMPRODUCT formula. This approach allows you to apply multiple criteria simultaneously, ensuring that only cells meeting specific conditions are included in the final average.
The standard AVERAGEIF function does not support logical operators like "not equal to zero" directly when combined with other conditions in a way that ignores zeros effectively in all scenarios. Instead, using array formulas provides the necessary flexibility to filter out unwanted data points before averaging.
Understanding the Limitation of AVERAGEIF
The AVERAGEIF(range, criteria, [average_range]) function is powerful but has limitations when dealing with multiple conditions or complex exclusion rules. Specifically, it struggles to ignore zeros while checking another condition in a second range without returning incorrect results if the ranges are not perfectly aligned in size and shape.
When you attempt to use AVERAGEIF to ignore zeros, you might try something like =AVERAGEIF(A1:A10, "<>0"). While this works for a single range, combining it with a second range (e.g., checking if column B equals "Yes") becomes problematic because AVERAGEIF only accepts one criteria range. You cannot pass two different ranges as criteria sources in a single AVERAGEIF call.
The SUMPRODUCT Solution
The most robust method involves using SUMPRODUCT to calculate both the sum of the values and the count of valid entries separately, then dividing them. This mimics the behavior of an average while allowing for multi-condition filtering.
Assume you have:
- Range 1 (Criteria):
B2:B100containing categories (e.g., "Sales", "Marketing"). - Range 2 (Values):
C2:C100containing numerical values. - Goal: Average values in
CwhereBis "Sales" ANDCis not blank and not zero.
The formula structure is:
=SUMPRODUCT((B2:B100="Sales")*(C2:C100<>"")*(C2:C100<>0)*C2:C100) / SUMPRODUCT((B2:B100="Sales")*(C2:C100<>"")*(C2:C100<>0))This formula works by creating arrays of TRUE/FALSE values for each condition. Multiplying these arrays together converts TRUE to 1 and FALSE to 0. The first SUMPRODUCT sums the values that meet all criteria. The second SUMPRODUCT counts how many items met those criteria. Dividing the sum by the count yields the correct average, excluding blanks and zeros.
Step-by-Step Implementation
- Identify Your Ranges: Determine which column contains the criteria (e.g., department names) and which contains the numbers to be averaged.
- Define the Criteria: Decide what constitutes a "valid" entry. In this case, validity means the category matches your target and the value is neither empty nor zero.
- Construct the Numerator: Use
SUMPRODUCTto multiply the value range by the logical conditions. This ensures only qualifying numbers contribute to the total sum. - Construct the Denominator: Use another
SUMPRODUCTwith just the logical conditions (no value range). This counts the number of valid entries. - Divide: Place the numerator formula in the first part of the division and the denominator formula in the second part.
This method is dynamic. If you add new rows to your dataset, simply expand the range references in the formula. It also handles errors gracefully; if no values match the criteria, Excel will return a #DIV/0! error, which you can handle with IFERROR.
Handling Potential Errors
To prevent #DIV/0! errors when no data matches your criteria, wrap the entire formula in IFERROR:
=IFERROR(SUMPRODUCT(...) / SUMPRODUCT(...), 0)This returns 0 instead of an error code, making your dashboard cleaner. For more complex data manipulation, consider exploring How to automate subtotals in excel, which offers alternative ways to aggregate data based on categories.
Alternative: Using AVERAGEIFS
If your version of Excel supports AVERAGEIFS (Excel 2007+), you might think it solves the problem. However, AVERAGEIFS still requires the criteria range and the average range to be the same size and shape. More importantly, AVERAGEIFS cannot easily exclude zeros while applying a second criteria unless you treat "not zero" as a separate criterion.
You can use AVERAGEIFS like this:
=AVERAGEIFS(C2:C100, B2:B100, "Sales", C2:C100, "<>0", C2:C100, "<>")This tells Excel to average C where B is "Sales", C is not zero, and C is not blank. Note that you repeat the average_range (C2:C100) for every additional criteria. This is often cleaner than SUMPRODUCT for simple cases but can become unwieldy with many conditions.
Comparing Methods
- SUMPRODUCT: Best for complex logic, non-contiguous ranges, or when you need to perform calculations within the criteria.
- AVERAGEIFS: Cleaner syntax for straightforward multi-criteria averages but limited to matching ranges for criteria and averages.
For users struggling with basic formula syntax, reviewing Can/will anyone help me with a Formula can provide foundational insights into debugging common Excel errors.
Frequently Asked Questions
Why does my AVERAGEIF include zeros?
Standard AVERAGEIF includes zeros because they are numeric values. To exclude them, you must explicitly add a criteria that checks for "not equal to zero" using AVERAGEIFS or by filtering via SUMPRODUCT.
Can I use SUMPRODUCT with text criteria?
Yes, SUMPRODUCT handles text criteria just like numeric ones. You simply compare the text range to the desired string (e.g., Range="Text"), which returns TRUE/FALSE arrays that multiply correctly.
What if I get a #DIV/0! error?
This error occurs when no values meet your criteria, resulting in a denominator of zero. Wrap your formula in IFERROR(formula, 0) to display a clean zero instead of an error message.
Is AVERAGEIFS faster than SUMPRODUCT?
For small datasets, the difference is negligible. For very large datasets, AVERAGEIFS is generally optimized by Excel's engine and may perform slightly better than array-based SUMPRODUCT calculations.
How do I ignore negative numbers?
Add another criteria to your AVERAGEIFS function: C2:C100, ">0". In SUMPRODUCT, add (C2:C100>0) to the multiplication chain alongside your other conditions.