Forum Discussion
SUMIFS formulas in different Layout
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
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().
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 :
Forecast sheet:
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.
Himtarler
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.