SUMPRODUCT Dynamic Help

[St3ff3ns]

Dears,

I need your help to finish my Sumproduct Formula and get it dynamic and not dependent of the number of rows or columns in the Sheet containing the Data source,

the formula is contained in the Sheet Overview 2017, in the table E43:S60,
This formula is searching for the sales or FCST based on several criterias (Brand, Format, botlle type, Month number and year,Sales or FCST)
If Sales, this is returning the formula to sheet SPT, if FCST, to the sheet SPT FCST,

For the first part of formula, to retrieve the sales, i have no issues and it is dynamic, but for the Second part, if I need FCST info, i need the formula to be equal to the number of row or columns contained in the sheet SPT FCST, which drastically reduce the dynamic use of my file,

Could you please help me in correcting it ? I am running out of ideas,

In attached the concerned file,

thanks in advance !

Stef

[dflak]

I see one issue. I'm not sure how SUMPRODUCT deals with a multiple column reference such as ('SPT FCST'!$E$1:$R$1='Overview 2017'!$F$3).

I think you may have better luck with column headings such as Nov 16, Dec 16, Jan 17, etc.

Then you could use the offset command to point to the correct column using Match.

http://www.utteraccess.com/wiki/inde...Dynamic_Ranges

Also you might consider using Excel Tables.

http://www.utteraccess.com/wiki/inde...ables_in_Excel

Two of the advantages of tables to this issue are (1) tables know how big they are, so you don't have to guess at which row you need to stop. Formulas based on tables use all of the data they need and no more and (2) Formulas based on tables use the table header name instead of Row/Column addresses. You may be able to use this feature in conjunction with INDIRECT to match the table header on the Overview sheet with the table header on the SPT FCST Sheet. This should eliminate the need for OFFSET and MATCH.

For example if Cell F7 on the Overview sheet contained "Feb 17" and the name of the table on the SPT FCST sheet is Table_FCST then the formula may contain =SUMIFS(INDIRECT("Table_FCST["&F7&]"), ...)

[St3ff3ns]

Hi Dflak, thanks for your feedback,

Unfortunately my skills stop there and i still need to learn how to work with tables and formulas together,

Where i am a bit confused is why is it working for the sales part when retrieveing data from sheet SPT and why is not working for FCST from sheet SPT FCST?

Any suggestion on this ?

Thanks in advance !

[dflak]

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.

[newdoverman]

I modified the date structure on the SPT FCST and SPT worksheets and made the same modification to the date row referenced on the Overview 2017 worksheet. I made real dates instead of combining month names with years.
The formula that I came up with is an INDEX/MATCH/MATCH formula using concatenated values to be looked up against concatenated columns. This is an array formula so enter with Ctrl + Shift + Enter.

Please Login or Register  to view this content.

[newdoverman]

This file uses tables on SPT FCST and SPT worksheets. That should allow the references to be dynamic as I have changed the references to match the last row number of each table.