# Formula for multiple criteria on summing up columns

Options

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

• ✭✭✭✭✭✭
Options

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:

bassam.khalil2009@gmail.com

• Options

Thank you so much, Bassam!

• ✭✭✭✭✭✭
edited 08/26/21
Options