How to Calculate Average Using Column Headings and Subheadings in Excel
Question details
The user needs to calculate the average of values based on matching criteria in both a main column heading (row 1) and a subheading (row 2).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Averaging data across columns where the column headers have a two-tier structure (e.g., subject and test title), and both headers must match specific criteria to be included.
- Observed behavior
- The goal is to filter and average only the values that fall under the columns satisfying both the row 1 and row 2 header conditions.
Ensure your dataset is organized with the main headings in the first row and subheadings directly below them in the second row, and avoid using merged cells within your criteria ranges.
Use an Array Formula with AVERAGE and IF
Combine the AVERAGE and IF functions to evaluate both header rows simultaneously before calculating the average.
This method uses boolean logic within an array formula to check if the conditions in row 1 and row 2 are met simultaneously. If both match, it passes the corresponding data columns into the AVERAGE calculation.
Click on the blank cell where you want the calculated average to appear.
Type the formula =AVERAGE(IF((A1:F1="C")*(A2:F2="Wednesday"),A3:F8)), replacing "C" and "Wednesday" with your specific criteria. Adjust the ranges (A1:F1, A2:F2, A3:F8) to match the actual size of your dataset.
If you are using an older version of Excel, press Ctrl+Shift+Enter to evaluate the formula properly. In newer Microsoft 365 versions, simply press Enter to apply it as a dynamic array.
Easily Calculate Conditional Averages in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, allowing you to calculate complex averages across multiple heading criteria effortlessly. Enjoy a familiar interface and a comprehensive function library without any cost.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet document containing the two-tier headings.
- 2. Input the array formula: Select a blank cell and enter =AVERAGE(IF((A1:F1="Criteria1")*(A2:F2="Criteria2"),A3:F8)).
- 3. Calculate the result: Press Enter to instantly see the averaged results, thanks to WPS Spreadsheet's native dynamic array support.
Frequently Asked Questions
Can I use AVERAGEIFS instead of an array formula for this?
AVERAGEIFS is typically used when evaluating criteria within rows to average a single column. Since you are evaluating criteria across horizontal headers (columns) and averaging a 2D range underneath, the AVERAGE(IF(...)) array approach is required.
What if my subheadings are located in different rows?
You must adjust the row references in the formula to match your sheet. For example, if headings are in row 1 and subheadings are in row 3, change the logic to evaluate (A1:F1="Criteria1")*(A3:F3="Criteria2").
Why am I getting a #DIV/0! error with this formula?
A #DIV/0! error occurs if no columns match both heading criteria simultaneously, meaning the formula is trying to average an empty dataset. Double-check your spelling, spaces, and criteria references in the formula.
