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? …
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:
You may download a Sample File here.
How’s the formula work?
It’s the magic of SUMPRODUCT.
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! 🙂
The awesome online class <50 Ways to Analyze your Data> by Chandoo.org is now open, but closing on 17-May-2017 midnight, pacific time. If you want to become awesome in Excel, action now. Click here to view more details
Disclosure: I’ll make a small commission if join to class via my site, but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend.