Sheet Summary formula IF AND

Hi, I'm looking for a formula for my Sheet Summary that will count the instances of checks in a checkbox column (CD), but only if the Status column does not show Complete.

I've made a start with this, but it's #unparseable.

=COUNTIF(AND(CD:CD, "1", [Status, "Complete")

Many thanks!

Find more posts tagged with

Accepted answers

MCorbin

Use Countifs when you have 2 criteria:

(and you don't need the quotes around the 1)

=COUNTIFS(CD:CD,1,Status:Status,"Complete")

Sean Morgan

Hello @LisaB:-) ,

To exclude specific items when counting in a list, we will want to use the <> operator. This operator is used for "Does not Equal" instances.

The Formula may look something like this: =COUNTIFS(CB:CB, 1, Status:Status, <>"Complete")

Furthermore, please see my example sheet below demoing this formula:

Please see this link for list of operators: https://help.smartsheet.com/learning-track/get-started/formula-basics

Let me know if you have any questions!

Regards

Sean

All comments

MCorbin

Use Countifs when you have 2 criteria:

(and you don't need the quotes around the 1)

=COUNTIFS(CD:CD,1,Status:Status,"Complete")

LisaB:-)

That's perfect, thank you!

How could I then exclude 'Completes' from the formula? So the Status could be: 'Not Started', or 'In Progress', or 'On Hold', but NOT 'Complete'?

LisaB:-)

Just bumping this up.

Sean Morgan

Hello @LisaB:-) ,

To exclude specific items when counting in a list, we will want to use the <> operator. This operator is used for "Does not Equal" instances.

The Formula may look something like this: =COUNTIFS(CB:CB, 1, Status:Status, <>"Complete")

Furthermore, please see my example sheet below demoing this formula:

Please see this link for list of operators: https://help.smartsheet.com/learning-track/get-started/formula-basics

Let me know if you have any questions!

Regards

Sean

LisaB:-)

This is really good, thank you Sean. I didn't know about <> before, and will use it much more in the future.

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.