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:
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.
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.
To sum numbers based on cells being equal to a certain value, you can use either the SUMIF or SUMIFS functions. In the example shown, we are summing all sales in the West region. The formula in cell H6 is: = SUMIF ( region , "West" ,...
The Excel SUMIF function returns the sum of cells that supplied criteria. Criteria can be applied to dates, numbers, and text using logical operators (>,,=) and wildcards (*,?) for partial matching....
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.