How to Use SUBTOTAL with AVERAGEIF in Excel

To use SUBTOTAL with AVERAGEIF in Excel, you need to combine the functions by using a helper column. The reason is that the SUBTOTAL function does not work directly with AVERAGEIF. Follow the steps mentioned below to combine these functions:

Step 1: Create the Helper Column

1. Open your Excel workbook and select an empty column where you want to insert the helper column.
2. In the first cell of the helper column, type the following formula (assuming the range of values you want to average is in column A and the criteria you want to apply are in column B):

=IF(B1="criteria",A1,"")

Replace "criteria" with the actual criteria you want to use for AVERAGEIF.

3. Press Enter and fill the formula down the entire helper column by double-clicking the bottom-right corner of the cell.

Step 2: Use the SUBTOTAL Function

1. In an empty cell, type the following formula:

=SUBTOTAL(1,helper_column_range)

Replace "helper_column_range" with the actual range of cells in the helper column.

2. Press Enter.

This formula will return the count of non-empty cells in the helper column.

Step 3: Calculate the Total

1. In an empty cell, type the following formula:

=SUBTOTAL(9,helper_column_range)

Replace "helper_column_range" with the actual range of cells in the helper column.

2. Press Enter.

This formula will return the sum of values in the helper column.

Step 4: Calculate the Average

1. In an empty cell, type the following formula:

=total_value/count_value

Replace "total_value" with the cell containing the total value from step 3 and "count_value" with the cell containing the count value from step 2.

2. Press Enter.

This formula will return the average value based on the criteria you set for AVERAGEIF.

Example

Let's assume we have a dataset in columns A and B, and we want to calculate the average of column A values if the corresponding value in column B is "Yes".

1. Create a helper column in column C with the formula:

=IF(B1="Yes",A1,"")

Fill this formula down the entire column C.

2. Use the SUBTOTAL function to count the non-empty cells in the helper column:

=SUBTOTAL(1,C1:C10)

Assuming your helper column range is C1 to C10.

3. Calculate the total of the helper column:

=SUBTOTAL(9,C1:C10)

4. Calculate the average by dividing the total by the count:

=total_value/count_value

Replace "total_value" with the cell containing the total value from step 3 and "count_value" with the cell containing the count value from step 2.