Sum by group in a table

This tutorial shows how to sum by group in a table that returns the summed valued of the associated group against the first occurrence in a table by using an Excel formula, with the IF, COUNTIF and SUMIF functions

EXCEL FORMULA 1. Sum by group in a table

EXCEL

Sum by group in a table

=IF(COUNTIF($B$5:B5,B5)>1,"",SUMIF($B$5:$B$11,B5,$C$5:$C$11))
GENERIC FORMULA

=IF(COUNTIF(dynamic_group_rng,group)>1,"",SUMIF(group_rng,group,sum_rng))

ARGUMENTS
dynamic_group_rng: A dynamic range that contain the list of groups that you want to test the criteria against.
group_rng: A range of cells that contain the list of groups that you want to test the criteria against.
group: A group that is used to determine which of the cells, from the specified range, should be summed.
sum_rng: The range of cells with values that are associated with the groups that you want to sum from.

EXPLANATION
This formula finds the first occurrence of a grouper and sums the values associated with it from a table. For all the following occurrences of the same grouper the formula return a value.

RELATED TOPICS

Related Topic Description Related Topic and Description
Sum by group How to sum values by group
Rank by group How to rank a number by group

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
COUNTIF Function The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria
IF Function The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE
SUMIF Function The Excel SUMIF function returns the sum of all numbers in a specified range based on a single criteria