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.
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.
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!
Roger,, cud you plzz write the mechanism behind A5A6,