This post is trying to answer a question by Laura in the post of 2D SUMIF with two variables – one on column and one on row.
Here’s part of the question:
……I want to look up the department in the vertical list and the month in the horizontal list, but I want to say sum that month and x many months before… … the only issue is that I want to be able to specify how many months are picked up in the solution. So if I’ve got 100s of months across the top of my data set, and departments down the side, in my formula I want to pick up the last 12 months of data for a selected department ending in a selected month. So my formula needs to have the potential to pick up all the months, but then depending on the month I select, I want to sum just the 12 months to that month. Is that possible? …
If I have understood correctly, Laura is looking for something like the following screen-cast:
To tackle the request of “be able to specify how many months“, I suggest to use two variables: “From” and “To” to specify the sum range required. Nevertheless, this approach bases on an assumption that the header (in our example Period) are sorted. Otherwise the formula won’t work.
Here’s the formula:
=SUMPRODUCT((Item=A3)*(Period>=B3)*(Period<=C3),Data)
where
| Data | =$B$6:$M$13 |
| Item | =$A$6:$A$13 |
| Period | =$B$5:$M$5 |
You may download a Sample File here.
How’s the formula work?
It’s the magic of SUMPRODUCT.
=SUMPRODUCT((Item=A3)*(Period>=B3)*(Period<=C3),Data)
As you see, the formula is short, clean, easy to read and understand. Isn’t it? 😛
In short, the formula comes with two parts:
- Setting up the criteria – (Item=A3)*(Period>=B3)*(Period<=C3)
- Setting up the sum range – Data
Literally it means: Sum data where Item equals to the value in A3 between the Period of B3 and C3 (both inclusive). Make sense?
Although it sounds super simple, the logic behind is not. It requires understanding of SUMPRODUCT, the use of logical operators, and last but not least the logical use of TRUE/FALSE.
SUMPRODUCT is simply amazing. I am planning to write two posts about SUMPRODUCT to give more explanations in details how the above formula works. Stay tuned! 🙂
wmfexcel 
If the column headers are unique but are text that can’t be referenced using greater/less comparisons, then either of the following will work, where DeptName is the desired department name, Period is the month (header name) to end on and NumMo is the number of historical months (including Period) that are needed in the sum. We will assume the data is in a structured table named DeptData.
=SUM(OFFSET(DeptData[#All],MATCH(DeptName,DeptData[Department],0),MATCH(Period,DeptData[#Headers],0)-1,1,-1*NumMo))
=SUM(INDEX(DeptData,MATCH(DeptName,DeptData[Department],0),MATCH(Period,DeptData[#Headers],0)-NumMo+1):INDEX(DeptData,MATCH(DeptName,DeptData[Department],0),MATCH(Period,DeptData[#Headers],0)))
LikeLike
That is a very elegant solution! Thank you so much for answering my question so thoroughly. It works perfectly. I’d managed to get a formula to work with a sumif, 2 indexes and an offset, but this is much cleaner and far more pleasing. I’m keen to learn more about sumproduct so look forward to your future posts. Thanks again!
LikeLike
You are welcome. Glad it helps. 🙂
LikeLike