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
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.
mtarler Thank you this helped a lot
- 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
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 , ?
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