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,