How to Use Excel AVERAGEIFS Formula with Multiple Criteria Across Sheets
Question details
The user needs to calculate the average of a specific column for a given person located on another sheet, while explicitly excluding a certain category (Type C).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating conditional averages across different worksheets using inclusion and exclusion criteria.
- Observed behavior
- The goal is to successfully return the average amount for a specific person while filtering out unwanted data types using an Excel formula.
Ensure your source sheet is clearly named without special characters (or enclose it in single quotes in your formula) and verify that the data ranges for your criteria and average calculations are the exact same size.
Use the AVERAGEIFS Formula to Filter and Calculate Across Sheets
Apply the AVERAGEIFS function to reference multiple criteria across different worksheets, combining positive matches with exclusion logic.
The AVERAGEIFS function allows you to calculate the average of a range based on one or more conditions. When referencing another sheet, prefix the cell range with the sheet name followed by an exclamation mark. To exclude specific text, you can use the not equal to operator (<>).
Click on the cell in your destination sheet where you want the average result to appear.
Type the formula: =AVERAGEIFS('Type Sheet'!$C:$C, 'Type Sheet'!$B:$B, B1, 'Type Sheet'!$A:$A, "<>C"). Ensure you replace 'Type Sheet' with your actual source sheet name.
Press the Enter key to apply the formula and view the calculated average.
Click the small square at the bottom-right corner of the selected cell and drag the fill handle down to apply the formula for other people listed in your column.
Calculate Conditional Averages Easily in WPS Spreadsheet
WPS Spreadsheet provides robust, native support for all major Excel functions, including AVERAGEIFS. You can seamlessly calculate, analyze, and build formulas across multiple sheets using an intuitive interface.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple data sheets.
- 2. Access the Insert Function tool: Select your target cell, navigate to the 'Formulas' tab on the top ribbon, and click on 'Insert Function'.
- 3. Search for AVERAGEIFS: Type 'AVERAGEIFS' in the search bar of the dialog box and select it from the list.
- 4. Define your ranges and criteria: Use the function arguments dialog to select your Average_range, Criteria_range1, Criteria1, and any exclusion criteria simply by clicking the corresponding columns on your other sheets.
- 5. Apply and calculate: Click 'OK' to automatically generate the correct cross-sheet formula and display the average.
Frequently Asked Questions
Why does my AVERAGEIFS formula return a #VALUE! error?
A #VALUE! error in AVERAGEIFS typically occurs because the criteria ranges and the average range are not identical in size. Ensure all ranges in the formula (such as C:C and B:B) span the exact same number of rows and columns.
Can I use wildcards with the AVERAGEIFS function to exclude partial text?
Yes, you can use wildcards in your criteria string. For example, using "<>*test*" will exclude any cells that contain the word 'test' anywhere in the text.
How do I exclude blank cells in an AVERAGEIFS formula?
To exclude blank cells from your calculation, add an additional criteria range pointing to the column you want to check, and set the criteria string to "<>".
