How do you write a SUMIFS forumula with 3 criteria?

Lisa Burgess

Below is my formula, but I am not getting it right:

=SUMIFS({2020 OE Revenue Actual & Projected Range 1}, {2020 OE Revenue Actual & Projected Range 2}, "New Business", [({2020 OE Revenue Actual & Projected Range 3}, >12/31/19), ({2020 OE Revenue Actual & Projected Range 3}, < 02/01/20)])

Paul Newcome

There are a few minor tweaks...

You do not need the square brackets, and when referencing a date in Smartsheet, you have to use the DATE function. There is also an extra set of parenthesis that isn't needed tucked in there.

DATE(yyyy,mm,dd)

=SUMIFS({2020 OE Revenue Actual & Projected Range 1}, {2020 OE Revenue Actual & Projected Range 2}, "New Business", {2020 OE Revenue Actual & Projected Range 3}, > DATE(2019, 12, 31), {2020 OE Revenue Actual & Projected Range 3}, < DATE(2020, 02, 01))

Paul Newcome

There are a few minor tweaks...

You do not need the square brackets, and when referencing a date in Smartsheet, you have to use the DATE function. There is also an extra set of parenthesis that isn't needed tucked in there.

DATE(yyyy,mm,dd)

=SUMIFS({2020 OE Revenue Actual & Projected Range 1}, {2020 OE Revenue Actual & Projected Range 2}, "New Business", {2020 OE Revenue Actual & Projected Range 3}, > DATE(2019, 12, 31), {2020 OE Revenue Actual & Projected Range 3}, < DATE(2020, 02, 01))

Lisa Burgess

Your a genius! Thank you!!

Paul Newcome

Happy to help! 👍️