Prior Year SUMIFS with multiple dropdown selections

Anna Deschenes

Hi - I'm trying to figure out how to make this formula work. I've tried several different ways of listing the Status options and it keeps saying #UNPARSABLE.

I'm trying to get the dollar amount of rows that meet criteria in three columns: Source, Status, and Date Received. Source and Status are both multiple dropdown columns - for Source I want to use CONTAINS and then for Status I need to list several options that it should use.

Here is what I have currently:

=SUMIFS(TOR:TOR, Source:Source, CONTAINS("GSD", (@cell)), [Status]:[Status], OR(@cell = “Win”, @cell = “Win_Contract Negotiations”, @cell = “Submitted_Pending Award_BAFO”, @cell = “Submitted_Pending Award”, @cell = “Loss”, @cell = “Disqualified”, @cell = “Cancelled Procurement (Post Submission)”)), [Date Received]:[Date Received], IFERROR(YEAR(@cell), 0) = 2020)))

Thank you!

Paul Newcome

You just have a couple of extra parenthesis tucked in throughout.

I also see "smart quotes" here and there. Notice how some are slanted and some are straight up and down? The slanted ones come from programs such as Microsoft Word. These are "smart quotes" and they will break a formula. You will want to remove those and retype them either here in the community, in smartsheet directly, or a text editor such as Notepad.

I did remove the extra parenthesis below though.

=SUMIFS(TOR:TOR, Source:Source, CONTAINS("GSD", @cell), [Status]:[Status], OR(@cell = “Win”, @cell = “Win_Contract Negotiations”, @cell = “Submitted_Pending Award_BAFO”, @cell = “Submitted_Pending Award”, @cell = “Loss”, @cell = “Disqualified”, @cell = “Cancelled Procurement (Post Submission)”), [Date Received]:[Date Received], IFERROR(YEAR(@cell), 0) = 2020)

Paul Newcome

You just have a couple of extra parenthesis tucked in throughout.

I also see "smart quotes" here and there. Notice how some are slanted and some are straight up and down? The slanted ones come from programs such as Microsoft Word. These are "smart quotes" and they will break a formula. You will want to remove those and retype them either here in the community, in smartsheet directly, or a text editor such as Notepad.

I did remove the extra parenthesis below though.

=SUMIFS(TOR:TOR, Source:Source, CONTAINS("GSD", @cell), [Status]:[Status], OR(@cell = “Win”, @cell = “Win_Contract Negotiations”, @cell = “Submitted_Pending Award_BAFO”, @cell = “Submitted_Pending Award”, @cell = “Loss”, @cell = “Disqualified”, @cell = “Cancelled Procurement (Post Submission)”), [Date Received]:[Date Received], IFERROR(YEAR(@cell), 0) = 2020)

Anna Deschenes

Oh, geeze - that's what I get for using a blank email. Thank you so much!

Paul Newcome

Happy to help. 👍️