# 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

- Open your Excel workbook and select an empty column where you want to insert the helper column.
- 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.

- 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

- 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.

- Press Enter.

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

## Step 3: Calculate the Total

- 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.

- Press Enter.

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

## Step 4: Calculate the Average

- 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.

- 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".

- Create a helper column in column C with the formula:

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

Fill this formula down the entire column C.

- 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.

- Calculate the total of the helper column:

`=SUBTOTAL(9,C1:C10)`

- 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.