Forum Discussion
Solved
Multiple condition statement?
I haven't used Excel in 5+ years. I'm trying to set up a spreadsheet for a school that itemises and provides costings for maintenance. There are likely to be 12 sheets; each with about 10 columns and...
Thank you - much appreciated.
=SUMIFS('Teaching Block'!$C$2:$C$4,'Teaching Block'!$A$2:$A$4,'Headline Sheet'!A2,'Teaching Block'!$B$2:$B$4,1)
+SUMIFS('Main School'!$C$2:$C$4,'Main School'!$A$2:$A$4,'Headline Sheet'!A2,'Main School'!$B$2:$B$4,1)
+SUMIFS('Science Block'!$C$2:$C$4,'Science Block'!$A$2:$A$4,'Headline Sheet'!A2,'Science Block'!$B$2:$B$4,1)I'm afraid a 3D reference across several sheets with SUMIFS formula isn't possible. If you work with legacy Excel such as Excel 2013 you can add the results of several SUMIFS. If you work with Office 365 or Excel for the web you can use dynamic formulas such as VSTACK and FILTER.
alternatively with Excel365 you could use a VSTACK and then a PIVOTBY:
=LET(alldata, VSTACK('Teaching Block:Science Block'!A2:C10),
data,FILTER(alldata,TAKE(alldata,,1)<>""),
PIVOTBY(TAKE(data,,1),CHOOSECOLS(data,2),TAKE(data,,-1),SUM))you can turn the Totals off if you don't want to see either or both of those.
Thank you - much appreciated.