Note: data must be sorted by the grouping column to get sensible results.
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.
Both formulas use built-in functions to calculate a subtotal, but the syntax used by SUMIF and SUMIFS is slightly different: SUMIF ( range , criteria , sum_range ) SUMIFS ( sum_range , range , criteria ) In both cases, note that the region "West"...
The Excel SUMIF function returns the sum of cells that meet a single condition. Criteria can be applied to dates, numbers, and text. The SUMIF function supports 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.