CrossTab SUM with 3 variables

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:Excel Tips - CrossTab SUM with 3 variables

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:

  1. Setting up the criteria – (Item=A3)*(Period>=B3)*(Period<=C3)
  2. 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! 🙂

[Affiliate news]

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.

Advertisements
This entry was posted in Formula and tagged . Bookmark the permalink.

3 Responses to CrossTab SUM with 3 variables

  1. David N says:

    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)))

    Like

  2. Laura Wood says:

    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!

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s