How to Average Cells That Meet One of Two Conditions in Excel
Question details
Calculate the average of values that meet at least one of two specific criteria, including scenarios where cells are highlighted by conditional formatting.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Performing mathematical averages on datasets where data needs to be filtered by multiple OR conditions rather than strict AND conditions.
- Observed behavior
- The user wants to find the average using logical OR conditions but realizes that conditional formatting colors only change appearance and cannot be used directly as a calculation condition.
Identify the exact mathematical or logical rules used in your conditional formatting, as Excel formula functions rely on underlying cell values rather than applied cell colors.
Use SUMIF and COUNTIF for OR Conditions
Since AVERAGEIFS applies strict AND logic, using a combination of SUMIF and COUNTIF allows you to average cells meeting one of two conditions (OR logic).
If you want to average cells where the value is either one condition or another, AVERAGEIFS will not work because it requires all conditions to be true simultaneously. Instead, calculating the total sum of those items and dividing it by their total count provides an accurate average.
Click on an empty cell where you want to output the calculated average.
Type the formula: =SUM(SUMIF(A2:A10, {"Condition1","Condition2"}, B2:B10)) / SUM(COUNTIF(A2:A10, {"Condition1","Condition2"})).
Replace A2:A10 with your criteria range, B2:B10 with your values range, and replace the conditions with your actual text or numbers.
Press Enter to calculate the average of the cells that meet either condition.
Use AVERAGEIF Based on Conditional Formatting Rules
When dealing with conditionally formatted cells, apply the exact same mathematical rule to the AVERAGEIF function.
Calculate Complex Averages Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical formulas, array calculations, and statistical functions like AVERAGEIF and SUMPRODUCT. You can seamlessly process multiple conditions and replicate complex Excel workflows with zero learning curve.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
- 2. Navigate to Formulas: Select the target cell, then go to the 'Formulas' tab on the top ribbon.
- 3. Insert Function: Click 'Insert Function' and search for SUMIF or AVERAGEIF.
- 4. Configure Criteria: Follow the intuitive dialog box to input your ranges and multiple conditions, then click 'OK' to generate your exact average.
Frequently Asked Questions
Can I average cells based strictly on their background color?
Spreadsheet formulas cannot natively calculate averages based on conditional formatting colors. You must either use the mathematical rule that triggered the color inside an AVERAGEIF function, or use VBA macros to sum by the color property.
Why does AVERAGEIFS return 0 or a #DIV/0! error when I use two conditions for the same column?
The AVERAGEIFS function uses strict AND logic. If you apply two mutually exclusive conditions to the same column (e.g., equal to 'Apple' and equal to 'Orange'), it looks for a cell that is simultaneously both, which is impossible, resulting in zero matching cells.
How do I average cells while ignoring zeroes?
You can use the AVERAGEIF or AVERAGEIFS function to exclude zeroes by setting the criteria to "<>0". For example: =AVERAGEIF(B2:B10, "<>0").
Is there an easier way to average with OR logic in newer versions?
Yes, if you have access to modern dynamic arrays, you can use the FILTER function wrapped in an AVERAGE function: =AVERAGE(FILTER(B2:B10, (A2:A10="Condition1")+(A2:A10="Condition2"))). The plus sign acts as the OR operator.
