Skip to Content

Sum Each Unique Cell And Show Total Only On The First Or Last Instance Of Each Unique Cell

How do you find the sum of data according to each unique instance of values in a column and then insert the sum calculation for each unique cell at the first or last instance?

Suppose you have a data set of sales staff and how much they received for each project, but then you needed to find out how much tax to withhold from their pay with this final calculation happening on their gross earnings for the month.

Here’s a simple demonstration illustrating the problem:

ABCD
1Employee NameSalesCommissionTax
2Smith, John4,000400
3Smith, John5,000750
4Smith, John10,0001,250
Sales and commission earnings per project for staff in a month

Imagine the data set continues with hundreds of other rows, and you need to display a total of the tax payable by employees, but only one instance of their total – not a total for each row.

To perform this type of calculation the COUNTIFS and SUMIFS function will be your best friend.

Calculate SUM On First Unique Cell

If you need to provide the total on each unique cell (in my case the Employee Name column) and it needed to be done on the first unique cell then the formula would look like this (in cell D2):

=IF(COUNTIFS($A$1:A2,A2)=1, SUMIFS(C:C,A:A,A2, "")

This formula checks the whole column of Employee Names and uses the COUNTIFS formula to find when the first instance of the Employee Name is found. As the cell is copied down and encounters other matching Employee Names it will not perform the SUMIFS operation as it would have counted more than 1.

ABCD
1Employee NameSalesComm.Tax
2Smith, John4,0004002,400
=IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "")
3Smith, John5,000750
=IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "")
4Smith, John10,0001,250
=IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "")
Applying the formula in the Tax column produces the result of getting a total at the first instance of the Employee Name

Calculate SUM On Last Unique Cell

You can also perform the same formula but have the total report on the last unique row for the individual cell. In cell D2 you would write the following formula:

=IF(COUNTIFS($A$1:A2,A2)=COUNTIFS(A:A,A2), SUMIFS(C:C,A:A,A2, "")

The difference between this formula and the previous is the change of the value 1 to COUNTIFS(A:A, A2). This calculation would then happen at the last count of the Employee Name.

ABCD
1Employee NameSalesComm.Tax
2Smith, John4,000400
=IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "")
3Smith, John5,000750
=IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "")
4Smith, John10,0001,2502,400
=IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "")
Applying the formula in the Tax column produces the result of getting a total at the last instance of the Employee Name

Summary

There are times when on the active spreadsheet you need to provide a total according to the unique value of another cell. Coupling the COUNTIFS formula with the SUMIFS formula can help provide this solution in an easy and understandable way.