Forum Discussion
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 150 lines. I need an expression that:
looks at a column to see if a cell contains e.g. 'ceiling' (a part of the classroom fabric).
Then looks at a second column to see if it contains '1' (Indicating high priority).
Then sums a column containing estimated costing in each sheet if the above are true to give an overall total.
I'm happy if it has to be done in blocks/stages i.e. something for each sheet that is then totalled.
Thanks in advance.
For example
Main school sheet:
A B C
Fabric Priority Cost
Ceiling 1 £ 5 000
External Walls 2 £ 2 500
Floor 1 £ 3 000
Science Block sheet
A B C
Fabric Priority Cost
Ceiling 1 £ 2 000
External Walls 2 £ 1 000
Floor 1 £ 1 000
Teaching Block sheet
A B C
Fabric Priority Cost
Ceiling 2 £ 4 000
External Walls 1 £ 500
Floor 2 £ 1 000
Headline Sheet (which will give an overview of total cost)
A B C
Fabric Priority 1 Priority 2
Ceiling £ 7 000 £ 4 000
External Walls £ 500 £ 3 500
Floor £ 4 000 £ 1 000
Thank you - much appreciated.
4 Replies
=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.
Thank you - much appreciated.
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.
