COUNTIFS with NOT CONTAINS from a Referenced sheet with two columns selected

Hello! I've searched through a few community posts but cannot seem to find an answer to my formula

I'm trying to calculate how many products we have in one column that are not "done" from another column that are both being referenced from the same sheet

where:

{product} = Product1, Product2

{status} = Done, Ready for Acceptance, In Progress, To Do, Requested

right now I have my formula as:

=COUNTIFS({product}, "Product1", {status}, NOT(CONTAINS("Done", {status})))

I've tried multiple variations of this and can't seem to figure it out. I've also tried just contains with adding all the other selections except Done and that hasn't worked either. I know I read somewhere about the limitations of using CONTAINS in COUNTIFS, so happy to try another formula if someone can figure it out!

Many thanks

Find more posts tagged with

Sheets

Formulas

Project Management

Accepted answers

Ramzi K

@Madelyn Rubenstein

Maybe you're overcomplicating things - try this (assuming your status column can only contain one value):

=COUNTIFS({product}, "Product1", {status}, <> "Done")

I hope this helps.

Cheers,

Ramzi

All comments

Ramzi K

@Madelyn Rubenstein

Maybe you're overcomplicating things - try this (assuming your status column can only contain one value):

=COUNTIFS({product}, "Product1", {status}, <> "Done")

I hope this helps.

Cheers,

Ramzi

Madelyn Rubenstein

Hi Ramzi,

Thanks so much for your response- this worked!

I do have another instance in which I want to show which countries {market} are not "done", but a user can select multiple countries per cell, where the selections are US, EU, JP, CN.

If I want to do the same thing, where I show 4 columns to represent the 4 regions for rows that are not "done", is it the same formula or different now that I have to account for multiple options?

Thanks!

Ramzi K

@Madelyn Rubenstein

So one column with selected regions (multiselect) and then one column for each separate region's status?

Maybe a screenshot will help.

Cheers,

Ramzi

Madelyn Rubenstein

Hi Ramzi,

See below- with the formula you provided above I think its not including rows that have multiple markets selected. Each Row is a different product (Product 1, Product 2, etc.) but in this case I just want to know the total products for each region that are not "Done". let me know if that helps

Ramzi K

@Madelyn Rubenstein

You would want to use the CONTAINS function. So maybe something like this:

=COUNTIFS({product}, "Product1", {status}, <> "Done", Market:Market, CONTAINS("US", @cell))

this would be for the US for example.

Does that help?

Cheers,

Ramzi

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.