How to Use SUBTOTAL with SUMIF in Excel

To use SUBTOTAL with SUMIF in Excel, you need to follow these steps:

1. First, create a helper column in which you calculate the subtotals for each group of data you want to sum up. To do this, use the SUBTOTAL function in the following format:

=SUBTOTAL(9, range)

Where 9 is the function number for SUM in the SUBTOTAL function, and range is the range of cells you want to sum up.

2. Next, use the SUMIF function to sum up the values in the helper column based on your given criteria. The SUMIF function should be in the following format:

=SUMIF(range, criteria, sum_range)

Where range is the range of cells you want to apply the criteria to, criteria is the condition you want to match, and sum_range is the range of cells containing the subtotals from the helper column.

Example

Let's say you have the following data in Excel:

A      B       C
1  Group  Value  Subtotal
2  A      10
3  A      20
4  B      30
5  A      40
6  B      50

You want to calculate the subtotal for each group (A and B) and then use SUMIF to sum up the subtotals for group A only.

1. First, create a helper column in column C to calculate the subtotals for each group. Use the SUBTOTAL function in cell C2:

=SUBTOTAL(9, B$2:B2)

2. Drag this formula down to fill the other cells in column C. Your data will now look like this:

A      B       C
1  Group  Value  Subtotal
2  A      10     10
3  A      20     30
4  B      30     30
5  A      40     70
6  B      50     80

3. Now use the SUMIF function to sum up the subtotals for group A only. In a new cell (e.g., E2), enter the following formula:

=SUMIF(A2:A6, "A", C2:C6)

4. Press Enter, and Excel will return the sum of the subtotals for group A, which is 70 in this example.

Remember to adjust the cell ranges according to your data set.