Forum Discussion

excel_learner's avatar
excel_learner
Brass Contributor
Nov 17, 2021
Solved

SUMIFS formulas in different Layout

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.

 

excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Nov 21, 2021

    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

16 Replies

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Nov 17, 2021

    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.

      • mtarler's avatar
        mtarler
        Silver Contributor
        Nov 17, 2021

        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.

Resources