Forum Discussion
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.
So each cell above would be base on giving me the sum total (Area m2) based on :
LEVEL 1, 2 or 3 (column E) then by DEPARTMENT (column F) then by USE FUNCTION (column G)
At the moment I'm going thru a general filter and doing old school C1+C2+C3... for all 300 rows. I have tried different formulas but cant seem to get multiple ones to work at the same time in the same cell.
Any help would be greatly appreciated. I can send the spreadsheet thru if you want to see how im doing it.
Thank you for any help.
=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.
- 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.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".
- If you have excel365 then you can use the FILTER function. Although you could do it all in a single formula lets do the more simple formula that you can copy across and down:
=SUM(FILTER(Schedule!$C:$C, (Schedule!$E:$E=$A$1)*(Schedule!$F:$F=B$1)*(Schedule!$G:$G=$A2),0))
note this is untested and meant to show you basically how to do it. Hope that works for you.- Hi,
Nope that formula did not work. spreadsheet attached if you want to see the full thing and how I have set it up. Probably not the best way to go about it but I'm self taught and going with what I know.
