Sumifs with criterion range in column with multiple entry options

Shawn L.

I track all of our projects on a single sheet, to include opportunities and finished projects. I want to sum the total sold amount. I have a column called "project status" that has 11 options for various status descriptions (i.e. pipeline, proposed, awarded, booked, complete, etc.). I'm trying to use this column as the criteria to sum the total sold, but I can't find a formula combination that allows me to use multiple entries in a column cell as criteria. I tried:

=sumifs([Contract Price]:[Contract Price], [Project Status] = "Awarded", [Project Status] = "Booked", [Project Status] = "Installing")

Do I need an OR statement in here? My preference is not to add a hidden helper column if I can help it.

Shawn L.

I've also tried the following:

=SUMIFS([Contract Price]1:[Contract Price]200, Project Status1:Project Status200, OR(@cell= “Awarded”, @cell=“Booked”, @cell =“Manufacturing”, @cell =“Installing”, @cell=“Closed”, @cell=“Complete”))

Unparseable every time.

Kleerfyre

So this is an example from the Smartsheet Formula Handbook and it should help you to re-write your formula:

=SUMIFS(Result199:Result203, Example199:Example203, "EMEA", [Date Data]199:[Date Data]203, MONTH(@cell) = 6)

This is also the helper text that explains how to build a SUMIFS formula:

SUMIFS( range, criterion_range1, criterion1, [criterion_range2, ​criterion2, ​...] )

You have to give a range for each of your criterion and then it will sum them based on your statuses needed.

this is how one of your criterion should look: [Project Status]:[Project Status], "Awarded"

You will have to do that for each one.

Shawn L.

@Kleerfyre thanks. I've tried just the basic SUMIF notation. The challenge I have is once I add a second criteria from that same Project Status column the sum comes out to $0.00.

=SUMIFS([Contract Price]1:[Contract Price]200, [Project Status]1:[Project Status]200, "Awarded", [Project Status]1:[Project Status]200, "Booked")

Kleerfyre

That's because its looking for ones that have both Awarded and Booked as the status. You would have to split it out or use an OR statement.

=SUMIF([Project Status]1:[Project Status]200, OR("Awarded", "Booked"),[Contract Price]1:[Contract Price]200)

Try that one.

Edit:

This is the correct formula:

=SUMIF([Project Status]:[Project Status], ="Awarded", [Contract Price]:[Contract Price]) + SUMIF([Project Status]:[Project Status], ="Booked", [Contract Price]:[Contract Price])

Paul Newcome

You will need to incorporate an AND statement.

=SUMIFS([Contract Price]1:[Contract Price]200, [Project Status]1:[Project Status]200, AND(@cell = "Awarded", @cell = "Booked"))

Shawn L.

I ended up using the SUMIFS with OR formula. My original version of this had an error. For some reason the column names were not actually linked. I went back and rewrote the formula and it worked. I appreciate your time on this. New to SmartSheet and trying to learn the notation as quickly as I can.

=SUMIFS([Contract Price]1:[Contract Price]222, [Project Status]1:[Project Status]222, OR(@cell = "Awarded", @cell = "Booked", @cell = "Manufacturing", @cell = "Installing", @cell = "Closed", @cell = "Complete"))

Paul Newcome

Sorry about that. I do the AND/OR mix-up in my sheets all the time. You'd think by now I wouldn't. Haha