For convenience only,data(C5:E16) andgroup(B5:B16) arenamed ranges.
If you are using an older version of Excel, use the SUMPRODUCT option.
The section on advanced criteria covers both options.
The reason is that SUMIFS expectssum_rangeto be the same size ascriteria_range.
What about the SUMIF function?
Basically, SUMIF resizessum_rangeto match therangeargument.
This kind of “silent failure” is dangerous because the resultseems reasonablebut is, in fact,incorrect.
You may not like formula errors, but at least they tell you something is wrong!
FILTER solution
In the current version of Excel, a nice solution is theSUM functionwith theFILTER function.
As the formula is copied down, it calculates the correct sum for each group.
With just a single array to process, SUMPRODUCT sums the array and returns a final result: 526.
For more information about this particular logic,see this example.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
GROUPBY Function
The Excel GROUPBY function is designed to summarize data by grouping rows and aggregating values.
The result is a summary table created with a single formula.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
