**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.

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

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

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