Forum Discussion

RogerioC2021's avatar
RogerioC2021
Copper Contributor
Mar 14, 2024

Multiple IF CONDITIONS formula for different tabs

Hi Experts,  I am trying to set up a cross reference table in 1 tab [TOTALS] that will automatically calculate totals based on 3 criteria in a different tab [SCHEDULE] in the same spreadsheet.   ...
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Mar 14, 2024

RogerioC2021 

=IFERROR(SUM(FILTER(SCHEDULE[[Existing Floor Area (M²)]:[Existing Floor Area (M²)]],(TOTALS[[#Headers],[Level 1]:[Level 1]]=SCHEDULE[[Level]:[Level]])*(TOTALS[[#Headers],[Building Plant]]=SCHEDULE[[Department]:[Department]])*(TOTALS[@[Level 1]:[Level 1]]=SCHEDULE[[Use / Function]:[Use / Function]]))),"")

 

With structured reference you can apply this formula. The formula is in cell B2 and filled across range B2:J14.

RogerioC2021's avatar
RogerioC2021
Copper Contributor
Mar 15, 2024
Hi Oliver, Just been thru all the formulas again trying out different variations and changing names and looking at the formulas. Still not working no matter what I do and Excel just keeps popping up the warning message THAT FUNCTION ISNT VALID.

Im using Microsoft Excel 2016. Does that make a difference with the formulas ?

Any help you can provide is greatly appreciated.
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Mar 16, 2024

    RogerioC2021 

    I would suggest a pivot table.

    "Level" and "Use /  Function" in rows area.

    "Department" in columns area.

    "Floor Area" ín values area.

     

    Set the report layout in tabular form. This could be not part of Excel 2016.

    Set subtotals only for "Level".

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 15, 2024

    RogerioC2021 

    =SUMPRODUCT((SCHEDULE[[Existing Floor Area (M²)]:[Existing Floor Area (M²)]])*(TOTALS[[#Headers],[Level 1]:[Level 1]]=SCHEDULE[[Level]:[Level]])*(TOTALS[[#Headers],[Building Plant]]=SCHEDULE[[Department]:[Department]])*(TOTALS[@[Level 1]:[Level 1]]=SCHEDULE[[Use / Function]:[Use / Function]]))

     

    The FILTER function isn't available in Excel 2016. In my first reply i didn't know which version of Excel you are working with. In the attached Excel 2013 sample file i've applied SUMPRODUCT instead of FILTER.

     

Resources