# SUMIFS with Date Range and multiple Contains Criteria

Options

I am trying to write a SUMIFS formula with date range criteria and multiple contains criteria.

Data is pulled from a Sales Pipeline Data Sheet. The formula below would result in the sum of Order Total for those projects that have a Status of "planning" or 'bidding" AND have a Projected Invoice Date between 12/01/2021 and 12/31/2021.

Feels like I'm close but am getting an error.

=SUMIFS({Sales Pipeline Data Sheet Range 1}, {Sales Pipeline Data Sheet Range 2}, AND(CONTAINS("planning", @cell), {Sales Pipeline Data Sheet Range 2}, CONTAINS("bidding", @cell)), {Sales Pipeline Data Sheet Range 3}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2021)))

Sales Pipeline Data Sheet Range 1 = Order Total

Sales Pipeline Data Sheet Range 2 = Status

Sales Pipeline Data Sheet Range 3 = Projected Invoice Date

Thank you!

• ✭✭✭✭✭✭
Options

Try this...

=SUMIFS({Sales Pipeline Data Sheet Range 1}, {Sales Pipeline Data Sheet Range 2}, OR(CONTAINS("planning", @cell), CONTAINS("bidding", @cell)), {Sales Pipeline Data Sheet Range 3}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2021)))

• ✭✭✭✭✭✭
Options

Try this...

=SUMIFS({Sales Pipeline Data Sheet Range 1}, {Sales Pipeline Data Sheet Range 2}, OR(CONTAINS("planning", @cell), CONTAINS("bidding", @cell)), {Sales Pipeline Data Sheet Range 3}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2021)))

• Options

Paul, that did it! As always, I appreciate your help!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!