# Multiple IF CONDITIONS formula for different tabs

Copper Contributor

# 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.

6 Replies

# Re: Multiple IF CONDITIONS formula for different tabs

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.

# Re: Multiple IF CONDITIONS formula for different tabs

=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.

# Re: Multiple IF CONDITIONS formula for different tabs

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.

# Re: Multiple IF CONDITIONS formula for different tabs

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 ?

# Re: Multiple IF CONDITIONS formula for different tabs

=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.

# Re: Multiple IF CONDITIONS formula for different tabs

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".