# Tom’s Tutorials For Excel: Summing Sets of Identical Items

Tom’s Tutorials For Excel: Summing Sets of Identical Items

Here’s a formula method to subtotal numbers in one column for each set of items in another column.

A company’s quarterly sales are listed, with varying salespeople each quarter. At each change in quarter in column A, the numbers in column C for that quarter are totaled in column D.

In the picture, the active cell is D5, holding the formula
`=IF(A5<>A6,SUMIF(\$A\$5:A5,A5,\$C\$5:C5)-SUMIF(\$A\$4:A4,A5,\$D\$4:D4),"")`
which is copied down to cell D24. ###### 3 comments on “Tom’s Tutorials For Excel: Summing Sets of Identical Items”
1. Roger Govier says:

Hi Tom

From time to time I get “brain fade” and I fear that this phenomenon has struck me again on this occasion, as I cannot see why you require the second set of SUMIF calculations in your formula.

For this method to work, the data must be sorted in Quarter order.

As such, column D will always contain null values, apart from the occasions where the IF pat of the formula is true, hence
=IF(A5A6,SUMIF(\$A\$5:A5,A5,\$C\$5:\$C5),””)
will return the same result as
=IF(A5A6,SUMIF(\$A\$5:A5,A5,\$C\$5:\$C5)-SUMIF(\$A\$4:A4,A5,\$D\$4:\$D4),””)

Indeed, the formula does nod need to be altered as you copy down the page as the fixed formula
=IF(A5A6,SUMIF(\$A\$5:\$A\$24,A5,\$C\$5:\$C\$24),””)
will also return the correct result.

As SUMIF, and COUNTIF are both extremely efficient, and only ever incorporate the used range of a column within their calculation, I might have been inclined to just use whole column references such as

=IF(A5A6,SUMIF(\$A:\$A,A5,\$C:\$C),””)

But as I said, I am apt to “brain fade” these days and maybe I am missing something.

Best wishes
Roger

• Tom Urtis says:

Hi Roger, thanks so much for your comment. I see exactly what you mean, and you are right, as far as I can tell upon testing this again, I agree the second SUMIF function is not needed.

I will go back to my notes and see what conditions existed in an example for which I had seen the need for a second SUMIF subtraction. However, my example here is not as complex as that one was, and hence does not need the extra construction as you said. If I can find what caused me to think the extra SUMIF was needed I’ll post that. Thanks so much for your observation and correction, and for visiting my blog!

-Tom

2. Rajesh Sinha says:

Roger,, cud you plzz write the mechanism behind A5A6,