Sum by group in Excel

Explanation

To subtotal data by group or label, directly in a table, you can use a formula based on the SUMIF function.

In the example shown, the formula in D5 is:

=IF(B5=B4,"",SUMIF(B:B,B5,C:C))

Note: data must be sorted by the grouping column to get sensible results.

How this formula works

The framework of this formula is based on IF, which tests each value in column B to see if its the same as the value in the “cell above”. When values match, the formula returns nothing (“”). When values are different, the IF function calls SUMIF:

SUMIF(B:B,B5,C:C)

In each row where SUMIF is triggered by IF, SUMIF calculates a sum of all matching rows in column C (C:C). The criteria used by SUMIF is the current row value of column B (B5), tested against all of column B (B:B).

Full column references like this are cool and elegant, since you don’t have to worry about where the data begins and ends, but you need to be sure there’s not extra data above or below the table that may get caught by SUMIF.