Sum Only the Cells That Meet Certain Criteria

steven.nienaber105336

I want to sum the Estimated Number of Work Hours in a column, but only in those rows where the Status says "In Progress". Is this possible? Thank you in advance!

Paul Newcome

For your range in the same sheet, you want to enter the column name(s) separated by a colon. To reference an entire column, you would use

[Column Name]:[Column Name]

Which means that to correct the #UNPARSEABLE error, you just need to correct the new range to read as

[Overall Resources Needed (Primary Task/Sub Tasks)]:[Overall Resources Needed (Primary Task/Sub Tasks)]

=SUMIFS([Estimated Project Work Hours]:[Estimated Project Work Hours], Status:Status, "In Progress", [Overall Resources Needed (Primary Task/Sub Tasks)]:[Overall Resources Needed (Primary Task/Sub Tasks)], “Jeremiah Hammen”)

EDIT:

I also noticed something else after posting my comment. If you compare the quotes around Jeremiah Hammen to the quotes around In Progress, you will see the first set is straight up and down but the set around the name are slanted to show open quotes vs closed quotes. These slanted quotes are called "Smart Quotes". They come from programs such as Microsoft Word. Smart Quotes will break a formula every time.

I am not sure what the other ones are called (not-smart quotes?), but to get those you would need to use a text program such as Notepad, type directly into Smartsheet, or type here in the Community.

Here is the formula with the range fixed AND the quotes as well.

=SUMIFS([Estimated Project Work Hours]:[Estimated Project Work Hours], Status:Status, "In Progress", [Overall Resources Needed (Primary Task/Sub Tasks)]:[Overall Resources Needed (Primary Task/Sub Tasks)], "Jeremiah Hammen")

Paul Newcome

Yes. It would look something like this...

=SUMIFS([Estimated Number of Work Hours]:[Estimated Number of Work Hours], Status:Status, "In Progress")

steven.nienaber105336

That worked, thank you Paul!

as a note, I must say how impressed, appreciative, and thankful for you and the others who provide these responses in SUCH a quick time frame! All of you are GREAT!

steven.nienaber105336

One more criteria I forgot to add - I only want it to count when it's assigned to a certain person. I get the #UNPARSEABLE message with the following formula:

"=SUMIFS([Estimated Project Work Hours]:[Estimated Project Work Hours], Status:Status, "In Progress", [Overall Resources Needed (Primary Task/Sub Tasks)], “Jeremiah Hammen”)

Thank you in advance!

Paul Newcome

For your range in the same sheet, you want to enter the column name(s) separated by a colon. To reference an entire column, you would use

[Column Name]:[Column Name]

Which means that to correct the #UNPARSEABLE error, you just need to correct the new range to read as

[Overall Resources Needed (Primary Task/Sub Tasks)]:[Overall Resources Needed (Primary Task/Sub Tasks)]

=SUMIFS([Estimated Project Work Hours]:[Estimated Project Work Hours], Status:Status, "In Progress", [Overall Resources Needed (Primary Task/Sub Tasks)]:[Overall Resources Needed (Primary Task/Sub Tasks)], “Jeremiah Hammen”)

EDIT:

I also noticed something else after posting my comment. If you compare the quotes around Jeremiah Hammen to the quotes around In Progress, you will see the first set is straight up and down but the set around the name are slanted to show open quotes vs closed quotes. These slanted quotes are called "Smart Quotes". They come from programs such as Microsoft Word. Smart Quotes will break a formula every time.

I am not sure what the other ones are called (not-smart quotes?), but to get those you would need to use a text program such as Notepad, type directly into Smartsheet, or type here in the Community.

Here is the formula with the range fixed AND the quotes as well.

=SUMIFS([Estimated Project Work Hours]:[Estimated Project Work Hours], Status:Status, "In Progress", [Overall Resources Needed (Primary Task/Sub Tasks)]:[Overall Resources Needed (Primary Task/Sub Tasks)], "Jeremiah Hammen")

steven.nienaber105336

Thanks Paul, I did notice that error after I sent my last comment, but I still get the #UNPARSEABLE response. Interesting thing, when I copy that formula out of the cell in Sheet Summary, it does not color the different columns like other formulas do.

=SUMIFS([Estimated Project Work Hours]:[Estimated Project Work Hours], Status:Status, "In Progress", [Overall Resources Needed (Primary Task/Sub Tasks)]:[Overall Resources Needed (Primary Task/Sub Tasks)], “Jeremiah Hammen”)

Thank you in advance!

Paul Newcome

Yes. The Sheet Summary Fields act a little differently sometimes with the colors.

See the edit to my last post. It has to do with the quotes.

Aparna Singh

I am using the same formula but my result is showing as 0, which is incorrect.

=SUMIFS([Story 1]:[Story 1], [Priority Status]:[Priority Status], "In Progress")

Paul Newcome

@Aparna Singh Exactly how is the data populated in each of the columns?