SOLVED

SUMIFS formulas in different Layout

Brass Contributor

Hi All,

 

I want to know if this is possible. I'm trying to create a formula for two rows (Forecast & Actuals) The Actual data input sheet is laid out in a way I can easily do the SUMIFS formula to get the required information in each cell. The trouble I'm having is the forecast input sheet has the weeks going across like this.

27

2829303132333435363738394041424344454647484950515253

 

I don't know how to structure the formula to show sum of forecasts for each row in my Master sheet using the adjacent rows for criteria.

Does anyone know how this can be done?

Any help would be great.

 

16 Replies

Hi @excel_learner
this table structure requires a relativly complex formula that includes the OFFSET-function. I have entered the formulas in column G of the Master sheet in the attached file.

 

But be aware that it checks only the week, the area and the category value in row 2 of the Master sheet. It does not consider the entries in column D ("LDP").

 

Hope, this helps.

Hi @Martin_Weiss
Is it possible to add one more criteria to the formula that being from LDP column ?

@excel_learner I created a 'master formula for that column using SWITCH instead of IFS.  as opposed to @Martin_Weiss using OFFSET I used INDEX which is probably 6 vs 1/2 doz.  I included the added criteria but my problem and why I didn't post earlier and had to come back to it is that I found out that your LDP values on the Master do not match your LDP values on the Forecast.  I could add even more complexity to the formula but I think it might be better for you to just pick a convention and use it.  Here is the formula:

=SWITCH($E10764,
"Forecast",IFERROR(SUMIFS(INDEX('Forecast Input'!$A:$BO,,MATCH(TEXT($A10764,"0"),'Forecast Input'!$1:$1,0)),'Forecast Input'!$A:$A,Master!$C10764,'Forecast Input'!$D:$D,Master!$D10764),0),
"Actual",SUMIFS('CMO Input'!$Q:$Q,'CMO Input'!$E:$E,Master!$A10764,'CMO Input'!$C:$C,Master!$C10764,'CMO Input'!$AJ:$AJ,Master!$D10764,'CMO Input'!$AU:$AU,Master!F$2),
"")

also it could be cleaner if you have Excel 365 and we could use LET() and FILTER() statements but you didn't tell us what version of Excel you are using.

In the attached you can see the formula and if you ctrl-down in column G you will find where I figured out the problem with the LDP naming.

Hi @mtarler 
Thank you I have now changed the LDP Values to match master sheet, when you said I could add even more complexity to the formula, in what way may I ask please ? I am using office 365, could you possibly show a cleaner formula with LET() and FILTER().

sorry my comment "I could add even more complexity" was intended w/r to adding additional parts to use partial matches to match the LDP values but you fixed that. As for using LET and FILTER I took a look and tried and find it marginal. I mean using LET you could 'define' all the ranges and such (but you could also do that using the Name Manager) so the formula is clearer what you are pointing to. As for the FILTER or using XMATCH or XLOOKUP function they don't seem to add much since the SUMIFS is doing most of that also. There is some marginal benefit since those functions give an optional parameter for 'if not found' instead of using IFERROR(). so unless there is other reasons, I don't see much point in making the conversion in this case but do recommend you try them out when you have a chance.

Hi @mtarler 

I have used the following formula but don't get any results for actuals also get the wrong totals on forecast for the specific categories.

 

The formula i used was 

=SWITCH($E6,"Forecast",IFERROR(SUMIFS(INDEX('Forecast Input'!$A:$BO,,MATCH(TEXT($A6,"0"),'Forecast Input'!$1:$1,0)),'Forecast Input'!$A:$A,Master!C6,'Forecast Input'!$D:$D,Master!D6),0),"Actual",SUMIFS('CMO Input'!$Q:$Q,'CMO Input'!$E:$E,Master!$A6,'CMO Input'!$C:$C,Master!$C6,'CMO Input'!$AJ:$AJ,Master!$D6,'CMO Input'!$AU:$AU,Master!$F2),"")

 

I also notice the section of the formula for forecast doesn't have the category criteria from master sheet. 

Do you know how I can fix this to get the actuals and forecast based on categories in cells F2, G2, H2 etc of the Master sheet based on criteria of week, Area, LDP, 

@excel_learner no it doesn't use category criteria because I didn't see you requesting that but now looking at it we have another matching issue:

Master sheet : mtarler_1-1637333881772.png

Forecast sheet:

mtarler_0-1637333812418.png

As for the Actual not getting calculated i just used your formula.  The problem appears that you didn't "lock" row 2 on the category conditional at the very end of the formula.  Basically $F2 should be F$2 so the column F can move to column G, etc... when you copy the formula to those columns but row 2 stays row 2 when you copy down the column.

Hi@mtarler 

Sorry silly mistake I did there, thank you for pointing that out though. 

If I was to add the categories (F2,G2,H2 and so on)  to the Forecast part of the syntax how can I do this?

This would be so I can get the forecast based on the category in each column.

 

Just add it into the SUMIFS portion of the "Forecast" formula. Below I created line breaks and spacing to help show the formula better and where to add the new condition. Note I also added "$" on Master!$C6 and $D6. Note you can delete all the spaces and line returns but you can also paste it directly into Excel if you click into the formula bar and hit paste there (i.e. if you click on a cell and hit paste it will spread it over multiple cells).
=SWITCH($E6,
"Forecast",IFERROR(SUMIFS(
INDEX('Forecast Input'!$A:$BO,,MATCH(TEXT($A6,"0"),'Forecast Input'!$1:$1,0)),
'Forecast Input'!$A:$A, Master!$C6,
'Forecast Input'!$D:$D, Master!$D6,
[[[ADD RANGE TO CHECK , CONDITIONAL]]] )
,0),
"Actual",SUMIFS(
'CMO Input'!$Q:$Q,
'CMO Input'!$E:$E, Master!$A6,
'CMO Input'!$C:$C, Master!$C6,
'CMO Input'!$AJ:$AJ, Master!$D6,
'CMO Input'!$AU:$AU, Master!F$2),
"")
  • @mtarler Thank you so much
    I have used this formula for each category I can for the time being.
    <=3" Actuals match CMO input sheet.
    4"-5" Actuals match CMO input sheet.
    6-7" Actuals don't match CMO input sheet
  • Totals on master sheet are 64,719
  • Totals on CMO input sheet is 64,869

Is there way I can do any back testing with the data to see where the problem is

 

 

I just looked at the columns being filtered to see which columns has additional names on the CMO sheet compared to the master. So using those drop down filters I saw the master has all the areas (and more). Then on the LDP columns I saw the CMO has 11 instead of 10 and identified "I & G" as the extra and when filtered for them found they account for 150 (the exact difference you found).
Hi
Yes correct thank you for spotting this. I also spotted the same kind of issue in forecast input sheet where there is also one extra LDP “LDP Blank” making the forecast input different to masters total for each category.
Will need to adjust the whole master sheet in this case as each any new name should have a unique combination for each week.

Could you possibly explain the workings of the Switch and index forecast formula and what each of the functions do in the forecast part of this formula.


best response confirmed by excel_learner (Brass Contributor)
Solution

sure. Switch is like a multiple IF statement. Instead of IF A=1 then ... ELSE IF A=2 then ... ELSE IF A=3 ... where you keep doing comparison against the same value, SWITCH just takes that one input and will SWITCH the output. If you type =SWITCH( into a cell then excel can show you the parameters but basically the value in question then pairs of condition, output so if the value meets a condition it will output that part.
INDEX will take an array or table and return the value or array that you specify. So an array of a,b,c,d,e,f and you index 3 then you get c. If it is a table you can specify both row and column to get a single value or just the row or just the col to get back the corresponding row or column. In your case here is the formula broken out with notes. I added a periods to create indenting but the word wrap may still make it confusing:
=SWITCH($E6, <<<this is the check value
"Forecast", <<<this is the first condition/check for switch
... IFERROR(SUMIFS(
...... INDEX('Forecast Input'!$A:$BO, <<< this is the range for the index
............. , <<< this is the row for the index (i.e. return all rows
.............. MATCH(TEXT($A6,"0"),'Forecast Input'!$1:$1,0)), <<< this match find the column you want for the index
........ 'Forecast Input'!$A:$A, Master!$C6, <<<this is a conditional pair for sumifs
........ 'Forecast Input'!$D:$D, Master!$D6, <<<this is a conditional pair for sumifs
........ [[[ADD RANGE TO CHECK , CONDITIONAL]]] ) <<<this is a conditional pair for sumifs
... ,0), <<< this is the value to return if error found
"Actual", <<<this is the second condition/check for switch
... SUMIFS(
......... 'CMO Input'!$Q:$Q,
......... 'CMO Input'!$E:$E, Master!$A6,
........ 'CMO Input'!$C:$C, Master!$C6,
........ 'CMO Input'!$AJ:$AJ, Master!$D6,
....... 'CMO Input'!$AU:$AU, Master!F$2),
"") <<< this is the default result for switch (i.e. if it didn't match any of the above

@mtarler Thank you

What does the part highlighted in red refer to

 MATCH(TEXT($A6,"0"),'Forecast Input'!$1:$1,0)), <<< this match find the column you want for the index  , ?

 

It is the row 1 on the Forecast sheet. basically find the column that has the title for the month you are interested in ($A6). I use TEXT($A6,"0") just to force A6 to be text so it will match. otherwise it won't find anything because 1 is a number and the other is text.

@mtarler  Thank you this helped a lot

1 best response

Accepted Solutions
best response confirmed by excel_learner (Brass Contributor)
Solution

sure. Switch is like a multiple IF statement. Instead of IF A=1 then ... ELSE IF A=2 then ... ELSE IF A=3 ... where you keep doing comparison against the same value, SWITCH just takes that one input and will SWITCH the output. If you type =SWITCH( into a cell then excel can show you the parameters but basically the value in question then pairs of condition, output so if the value meets a condition it will output that part.
INDEX will take an array or table and return the value or array that you specify. So an array of a,b,c,d,e,f and you index 3 then you get c. If it is a table you can specify both row and column to get a single value or just the row or just the col to get back the corresponding row or column. In your case here is the formula broken out with notes. I added a periods to create indenting but the word wrap may still make it confusing:
=SWITCH($E6, <<<this is the check value
"Forecast", <<<this is the first condition/check for switch
... IFERROR(SUMIFS(
...... INDEX('Forecast Input'!$A:$BO, <<< this is the range for the index
............. , <<< this is the row for the index (i.e. return all rows
.............. MATCH(TEXT($A6,"0"),'Forecast Input'!$1:$1,0)), <<< this match find the column you want for the index
........ 'Forecast Input'!$A:$A, Master!$C6, <<<this is a conditional pair for sumifs
........ 'Forecast Input'!$D:$D, Master!$D6, <<<this is a conditional pair for sumifs
........ [[[ADD RANGE TO CHECK , CONDITIONAL]]] ) <<<this is a conditional pair for sumifs
... ,0), <<< this is the value to return if error found
"Actual", <<<this is the second condition/check for switch
... SUMIFS(
......... 'CMO Input'!$Q:$Q,
......... 'CMO Input'!$E:$E, Master!$A6,
........ 'CMO Input'!$C:$C, Master!$C6,
........ 'CMO Input'!$AJ:$AJ, Master!$D6,
....... 'CMO Input'!$AU:$AU, Master!F$2),
"") <<< this is the default result for switch (i.e. if it didn't match any of the above

View solution in original post