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:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee Name | Sales | Commission | Tax |
| 2 | Smith, John | 4,000 | 400 | |
| 3 | Smith, John | 5,000 | 750 | |
| 4 | Smith, John | 10,000 | 1,250 |
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.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee Name | Sales | Comm. | Tax |
| 2 | Smith, John | 4,000 | 400 | 2,400=IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "") |
| 3 | Smith, John | 5,000 | 750 | =IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "") |
| 4 | Smith, John | 10,000 | 1,250 | =IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "") |
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.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee Name | Sales | Comm. | Tax |
| 2 | Smith, John | 4,000 | 400 | =IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "") |
| 3 | Smith, John | 5,000 | 750 | =IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "") |
| 4 | Smith, John | 10,000 | 1,250 | 2,400=IF(COUNTIFS($A$1:A2, A2)=1, SUMIFS(C:C,A:A,A2, "") |
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.
