Formula for multiple criteria on summing up columns

Desiree de los Reyes

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, one-time or ongoing cost. The sums I need to integrate are (1) total billable capital one-time cost, (2) total non-billable capital one-time cost, (3) total billable expense one-time cost, and (4) total non-billable expense one-time cost. Your expertise shared is most appreciated.

Desiree

Bassam Khalil

Hi @Desiree de los Reyes

Hope you are fine, please try the following formulas:

1- total billable capital one-time cost

=IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group],
@cell = "one-time cost", [Capital?]:[Capital?], @cell = "Yes", [Billable?]:[Billable?],
@cell = "Yes"), "")

2-total non-billable capital one-time cost

=IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group],
 @cell = "one-time cost", [Capital?]:[Capital?], @cell = "Yes", [Billable?]:[Billable?],
 @cell = "No"), "")

3-total billable expense one-time cost

=IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group],
 @cell = "one-time cost", [Billable?]:[Billable?], @cell = "Yes", [Cost Type]:[Cost Type],
 CONTAINS("expense", @cell)), "")

4-total non-billable expense one-time cost

=IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group],
@cell = "one-time cost", [Billable?]:[Billable?], @cell = "No", [Cost Type]:[Cost Type],
CONTAINS("expense", @cell)), "")

the following screenshot shows the result:

Desiree de los Reyes

Thank you so much, Bassam!

Bassam Khalil

@Desiree de los Reyes

You are welcome, and i will be happy to help you any time, Please help the Community by marking it as an ( Accepted Answer)