Count If Assigned to... and status not Complete (two columns criteria)

Hi All,

I have a column called 'Assigned To' - this contains smart sheet contacts from a drop-down. (can be multiple people) per task.

The second column is called 'Status' - It has a limited range of selections. Open, Complete etc (can only be one selection)

I am trying to create a formula that will solve this text description....

Count the total number of NOT "Complete" tasks for "Assigned Name", on the SAME action item row.

Any thoughts? I've reviewed the smart sheet sample formulas but cant seem to combine them to get what i need.

Thanks

Pete

Find more posts tagged with

Accepted answers

Andrée Starå

@Pete Hayward - FS - OCIT

Happy to help!

Try something like this.

=COUNTIFS([Assigned To]:[Assigned To], FIND("Pete", @cell) > 0, Status:Status, <>"4 Complete")

Did it work?

All comments

Bassam Khalil

Hi @Pete Hayward - FS - OCIT

Hope you are fine, i created a sample sheet for your case, please check the following formula and you can convert the columns name in your case using my formula as a reference:

=COUNTIFS([Assigned To]:[Assigned To], FIND([Contact to count for]@row, @cell) > 0, Status:Status, <>"Complete")

the following screenshot shows the result:

Pete Hayward - FS - OCIT

Hi Bassam,

Thanks for taking the time to help me. I have tried your formula but i'm getting the unparseable message. Here is my version...

=COUNTIFS([Assigned To]:[Assigned To], FIND([Pete]@row, @cell) > 0, Status:Status, <>"4 COMPLETE")

Note, the Complete is actually called "4 COMPLETE".

I also tried another version: (but the same error message)

=COUNTIFS([Assigned To]:[Assigned To], FIND("Pete" @row, @cell) > 0, Status:Status, <>"4 COMPLETE")

Any thoughts?

Thanks again, I feel like its close to a solution!

Pete

Bassam Khalil

Hi @Pete Hayward - FS - OCIT

Ithink you have problem with column type, Could you please share me as an admin to a copy of your sheet after you remove any sensitive data and I will solve your problem.

My Email: bassam.k@mobilproject.it @m

Andrée Starå

Hi @Pete Hayward - FS - OCIT

I hope you're well and safe!

To add to Bassam's excellent advice/answer.

Try something like this.

=COUNTIFS([Assigned To]:[Assigned To], FIND(Pete@row, @cell) > 0, Status:Status, <>"4 COMPLETE")

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

=COUNTIFS([Assigned To]:[Assigned To]; FIND(Pete@row; @cell) > 0; Status:Status; <>"4 COMPLETE")

Did that work/help?

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 help 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!

Pete Hayward - FS - OCIT

Hi Bassam & Andrée,

Sadly I cant share the sheet but that was a good idea! Here is an example of my issue.

So, I want the formula to tell me the total number of items that I have open, but i dont want it to include completed actions... So in this instance it should show 1 for Pete, 0 for Harry etc..

Thanks again for your efforts!

Pete

Andrée Starå

@Pete Hayward - FS - OCIT

Happy to help!

Try something like this.

=COUNTIFS([Assigned To]:[Assigned To], FIND("Pete", @cell) > 0, Status:Status, <>"4 Complete")

Did it work?

Pete Hayward - FS - OCIT

Awesome! this works, many thanks indeed to you both.

Pete

Andrée Starå

@Pete Hayward - FS - OCIT

Excellent!

You're more than welcome!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.