COUNTIFS function

Tony Nguyen

Hi All,

I have problem with Countifs in Smartsheet during it's still workable in Excell as well.

As below picture, I'd like to count product type is "Goalies Mask" with result "FAIL". I applied formula : =COUNTIFS([Product type]:[Product type],"Goalie Mask",[Result]1:[Result],"FAIL"))

but it shows #UNPARSEABLE.

Would you please to help me correct again formula if i'm wrong! Thank you!

Andrée Starå

Hi Tony,

You have the number 1 in the Result range and one to many closing parentheses in the end. Remove those, and it will work. Also, you can remove the [ ] around the Result because it’s one word and has no numbers or special characters.

Edit: Looking at the screenshot, it seems like you only need to remove the last parenthesis.

Try this.

=COUNTIFS([Product type]:[Product type], "Goalie Mask", Result:Result, "FAIL")

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@Tony Nguyen

Try something like this.

You actually don't need the Week column for the formula to work. At the end of the formula I added -0 and you can change that to -1 to look at last week. Would that work?

COUNTIFS

=COUNTIFS([Product type]:[Product type]; "Goalie Mask"; [Inspection Date]:[Inspection Date]; IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()) - 0)

SUMIFS

=SUMIFS([MAJOR defects Qty]:[MAJOR defects Qty]; [Inspection Date]:[Inspection Date]; IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()) - 0)

Did they work?

Andrée Starå

Hi Tony,

You have the number 1 in the Result range and one to many closing parentheses in the end. Remove those, and it will work. Also, you can remove the [ ] around the Result because it’s one word and has no numbers or special characters.

Edit: Looking at the screenshot, it seems like you only need to remove the last parenthesis.

Try this.

=COUNTIFS([Product type]:[Product type], "Goalie Mask", Result:Result, "FAIL")

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Tony Nguyen

Hi Andrée Starå,

Appreciated your help! It works perfect after your advice!

Thank you so much!

Andrée Starå

Excellent!

You're more than welcome!

Tony Nguyen

Hi Andrée Starå,

One more things I need from your help!

Kindly see below picture:

• I want to Sum data of product Goalie Mask with Major issue in Week 19. Current I setup manual formula to sort week 19 as : =SUMIFS([MAJOR defects Qty]:[MAJOR defects Qty]; Week:Week; "19"; [Product type]:[Product type]; "Goalie Mask"). It work as well. But I would to auto update last 7 days to replace manual sorting as formula: =SUMIFS([MAJOR defects Qty]:[MAJOR defects Qty]; WEEKNUMBER(TODAY()-7; [Product type]:[Product type]; "Goalie Mask"), It was not work. Would you please to help me correct again

• It also the same with COUNTIFS when I want to replace manual Count data of product Goalies mask with last 7 days as formula: =COUNTIFS([Product type]:[Product type]; "Goalie Mask"; WEEKNUMBER(TODAY()-7)

Thank you for your help!

Andrée Starå

@Tony Nguyen

Try something like this.

You actually don't need the Week column for the formula to work. At the end of the formula I added -0 and you can change that to -1 to look at last week. Would that work?

COUNTIFS

=COUNTIFS([Product type]:[Product type]; "Goalie Mask"; [Inspection Date]:[Inspection Date]; IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()) - 0)

SUMIFS

=SUMIFS([MAJOR defects Qty]:[MAJOR defects Qty]; [Inspection Date]:[Inspection Date]; IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()) - 0)

Did they work?

Tony Nguyen

Dear Andrée Starå,

Thank you so much for your supporting! Everything is perfect as I expected

Best regards,

Tony

Andrée Starå

@Tony Nguyen

Excellent!

I'm always happy to help!