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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
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,

Leave a Reply

Your email address will not be published. Required fields are marked *

*