Formula for multiple criteria on summing up columns
Hi, all! Does anyone know the formula for multiple criteria to be met before adding column values up? The data set example follows. I need to be able to total up 7 cost types if they are either billable or not, expense or capital, onetime or ongoing cost. The sums I need to integrate are (1) total billable capital onetime cost, (2) total nonbillable capital onetime cost, (3) total billable expense onetime cost, and (4) total nonbillable expense onetime cost. Your expertise shared is most appreciated.
Desiree
Answers

Hope you are fine, please try the following formulas:
1 total billable capital onetime cost
=IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group], @cell = "onetime cost", [Capital?]:[Capital?], @cell = "Yes", [Billable?]:[Billable?], @cell = "Yes"), "")
2total nonbillable capital onetime cost
=IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group], @cell = "onetime cost", [Capital?]:[Capital?], @cell = "Yes", [Billable?]:[Billable?], @cell = "No"), "")
3total billable expense onetime cost
=IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group], @cell = "onetime cost", [Billable?]:[Billable?], @cell = "Yes", [Cost Type]:[Cost Type], CONTAINS("expense", @cell)), "")
4total nonbillable expense onetime cost
=IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group], @cell = "onetime cost", [Billable?]:[Billable?], @cell = "No", [Cost Type]:[Cost Type], CONTAINS("expense", @cell)), "")
the following screenshot shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Thank you so much, Bassam!

You are welcome, and i will be happy to help you any time, Please help the Community by marking it as an ( Accepted Answer)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
Check out the Formula Handbook template!