Sum by group

Sum by group

Generic formula 

=IF(A2=A1,"",SUMIF(A:A,A2,B:B))

Related formulas 

Sum if cells are equal to

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.

Sum by group

Generic formula 

=IF(A2=A1,"",SUMIF(A:A,A2,B:B))

Related formulas 

Sum if cells are equal to

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.