SUMPRODUCT Dynamic Help

I’ll expand on what I suggested in my previous posts.

First, this is why I think the SUMPRODUCT isn’t working.

The issue you are running into is that SUMPRODUCT is looking for a single Column such as ('SPT FCST'!$E$1:$E$39='Overview 2017'!$F$3) which means "deal with all rows in E1:E39 where the cells in that row = F3.

What you are asking for is "deal with all rows in E1:R39" where the cells in that row = F3. Which one of these multiple columns do you want the match to occur in?

I don’t see where you are trying to use multiple columns in the actual sales, so that’s why it is working.

What you seem to be wanting to do is find the column where the header is the month and use it. This is a job for MATCH.

This is why I threw the OFFSET command at you. It works with MATCH. I’ll cite some examples below. It’s a bit clearer when you have something concrete and can look it at step by step.

So suppose you want to use the Column associated with December 2016. First you have to find out where this column is. That’s where match comes in: = MATCH(“December”,’SPT FCST’!3:3,0). This command will find the first occurrence of “December” in column F on the SPT FCST sheet. In this case, 5 (match gives the numerical position of the column).

But wait! What if you meant December 2017 (which is what I think you want)? How do you tell Match to get the second occurrence of “December?” Well, there are ways of doing this, but they are complicated.

An easier and more flexible way of doing this is to take the header columns January, February, March … on all sheets, and replace them with 1/1/2016, 2/1/2016 … 1/1/2017 … 12/1/2017. Then format the cells as “mmmm.” The cells will contain the actual date but appear like the string representation of the month. In other words, to the eye, the spreadsheet looks the same.

Now when you match the cell containing 12/1/2017, it is going to find its match in column R or the 18th column.

Match tells you where to find the column. This is one part of the needed information.

The rest of the story is completed by OFFSET. If you know a range, you can define a new range using offset with three arguments: OFFSET(Original Range, Rows Down, Columns Right). Match gives us the columns right.

Now we are ready to use offset. We know that MATCH(12/1/2017,’SPT FCST’!3:3,0) is 18. So we want to tell Excel, “start with column A and go 17 columns to the right and use that column). Offset starts counting at 0. Zero columns to the right of column A is column A (column number 1) One column to the right of column A is column B (column number 2). Match starts counting a 1. So although column R is column 18, it is offset 17 columns from column A.

The rows down for this example is easy. It’s zero. We don’t want to start any number of rows down. We want to start on the same row as the original range.

So for December 2017, the offset command we want to use is =OFFSET(A:A, 0, MATCH(…)), to use some shorthand.

Now it’s time for a “real” spreadsheet example.

Let’s get the forecast for January 2017 for the item shown in cell D43. The formula is:

=SUMIFS(OFFSET('SPT FCST'!$A$4:$A$101,0,MATCH(E$37,'SPT FCST'!$3:$3,0)-1),'SPT FCST'!$D$4:$D$101,$D43)

Let’s disassemble this formula from the inside out.

We already discussed the match. Cell E$37 contains 1/1/2017. This happens in column 7 of the SPT FCST sheet. We subtract 1 because offset counts from zero while match counts from 1.

So OFFSET('SPT FCST'!$A$4:$A$101,0,MATCH(…) -1) says Take the range A4:A101 on the SPT FCST sheet and go down zero rows and over 6 columns. In other words the offset part of the formula points to ‘SPT FCST’!$I$4:$I$101.

This is the range we want to sum. The other part 'SPT FCST'!$D$4:$D$101,$D43) is sort of a where clause (if you know SQL) where the cells in D4:D100 are equal to the contents of D43.

I partially converted your workbook to use dates – I did January 2017 on Overview 2017 and SPT FCST. Also I only did the forecast part of the formula.

The formula has enough absolute referencing that it can be copied to all cells. You can use the same logic for sales.

The offset is a little tricky, but you would have to use it anyway. The overall formula, SUMIFS is easier to “see” than SUMPRODUCT. SUMPRODUCT is more powerful, but we don’t need that power here.

In fact, you might want to change the formula to switch from sales to forecast based on a date and whether that date is greater than, equal to or less than the column header.