Exceljet

Quick, clean, and to the point

Sum by group

Excel formula: Sum by group
Generic formula 
=IF(A2=A1,"",SUMIF(A:A,A2,B:B))
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.

Performance

It may look like using a full column reference is a bad idea, since current versions of Excel contain more than 1m rows. However, testing has shown that Excel only evaluates data in the "used range" (A1 to the address of the intersection of the last used column and last used row) with this type of formula.

Charles Williams over at Fast Excel has a good article on this topic, with a full set of timing results.

Why about Pivot Tables?

This example is meant show how full column references work, and how they can be used directly in a data table. Pivot tables remain an excellent way to group and summarize data.
 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.