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

by Tom Urtis 3 Comments ↓

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.

‹ Tom’s Tutorials For Excel: Conditional Formatting the Nth Highest and Lowest Items in a List

Tom’s Tutorials For Excel: Getting Attention With Multiple Beeps ›

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Duplicates Uniques, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

3 comments on “Tom’s Tutorials For Excel: Summing Sets of Identical Items”

1. Roger Govier says:

   February 4, 2014 at 3:12 am

   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

   Reply
   • Tom Urtis says:

     February 4, 2014 at 7:20 pm

     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

     Reply
2. Rajesh Sinha says:

   November 19, 2017 at 9:20 am

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

   Reply