Calculate an Average for Selected Positions with SUMIFS and COUNTIFS in Excel
Question details
Calculate average sales per matching record for a given ID and selected positions.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An Excel table contains ID, Position, and Sales columns, and the user wants to include records for the BCT or LT positions.
- Observed behavior
- The desired result is total sales for the selected positions divided by the number of records matching the same ID and positions.
Check that your Excel table has columns named ID, Position, and Sales, and that sales values are numeric. The formula counts every matching record, including records with blank sales cells.
Divide SUMIFS totals by COUNTIFS for the selected positions
Use this formula in an Excel table to calculate sales per matching record for BCT and LT under the current row's ID.
In the Excel table containing ID, Position, and Sales, add a column for the average and select its first data cell.
Enter =IFERROR(SUM(SUMIFS([Sales],[ID],[@ID],[Position],{"BCT","LT"}))/SUM(COUNTIFS([ID],[@ID],[Position],{"BCT","LT"})),"") and press Enter.
Confirm that the formula appears in the table's result column and returns the combined sales for BCT and LT divided by their matching record count for each row's ID.
To show the result only on BCT or LT rows, wrap the formula in =IF(OR([@Position]="BCT",[@Position]="LT"),formula,"") and replace formula with the expression from the previous step.

Calculate average sales for selected positions in WPS Spreadsheets
Use SUMIFS and COUNTIFS with cell ranges in WPS Spreadsheets to divide combined BCT and LT sales by the number of matching records.
- 1. Open the sales data: Open your workbook in WPS Spreadsheets. For this example, place IDs in A2:A100, positions in B2:B100, and sales in C2:C100.
- 2. Enter the ID to match: Type the ID you want to analyze in cell F2.
- 3. Enter the average formula: Select an empty cell and enter =IFERROR((SUMIFS($C$2:$C$100,$A$2:$A$100,F2,$B$2:$B$100,"BCT")+SUMIFS($C$2:$C$100,$A$2:$A$100,F2,$B$2:$B$100,"LT"))/(COUNTIFS($A$2:$A$100,F2,$B$2:$B$100,"BCT")+COUNTIFS($A$2:$A$100,F2,$B$2:$B$100,"LT")),"").
- 4. Adjust the ranges: Change the ranges in the formula to cover your data, keeping the ID, Position, and Sales ranges aligned and the same size.

Frequently Asked Questions
Why use SUM around SUMIFS and COUNTIFS?
The array constant {"BCT","LT"} supplies two position criteria. SUM combines the results for both positions before the sales total is divided by the record count.
What happens if no records match the ID and positions?
The COUNTIFS total is zero, so division would produce an error. The IFERROR wrapper in the formula displays a blank instead.
Does the formula ignore records with blank sales?
No. COUNTIFS counts a matching ID and position even if Sales is blank. Make sure sales values are populated if each counted record should have a numeric value.
How can I use different position names?
Replace "BCT" and "LT" in the formula's position criteria with the names you need. Apply the same changes to both SUMIFS and COUNTIFS so their criteria match.




