Calculation Issues

Calculate an Average for Selected Positions with SUMIFS and COUNTIFS in Excel

Amos GikundaAmos Gikunda Nov 18, 2026 869 views

Question details

Calculate average sales per matching record for a given ID and selected positions.

Calculate an Average for Selected Positions with SUMIFS and COUNTIFS in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Add a result column

In the Excel table containing ID, Position, and Sales, add a column for the average and select its first data cell.

2
Enter the formula

Enter =IFERROR(SUM(SUMIFS([Sales],[ID],[@ID],[Position],{"BCT","LT"}))/SUM(COUNTIFS([ID],[@ID],[Position],{"BCT","LT"})),"") and press Enter.

3
Check the results

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.

4
Optionally limit displayed rows

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.

Divide SUMIFS totals by COUNTIFS for the selected positions
How matching records are counted: COUNTIFS counts matching rows even when Sales is blank or contains text, while SUMIFS does not add those values. IFERROR returns a blank when there are no matches, but it also hides other formula errors.
Calculate averages in WPS Spreadsheets

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. 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. 2. Enter the ID to match: Type the ID you want to analyze in cell F2.
  3. 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. 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.
Calculate sales per matching record for multiple positionsOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet functions and cell rangesWork in a free, easy-to-use office suite
microsoft office alternative - wps office

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.