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
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
    will return the same result as

    Indeed, the formula does nod need to be altered as you copy down the page as the fixed formula
    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


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

    Best wishes

    • 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!


  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 *