Countifs in Sheet Summary Not correct Result

Hi,

This formula is in my project plan summary sheet. I want to count the rows where Level = 2.1 or Level = 3.1 and Status = "Not Started.

This is my formula:

=COUNTIFS(Status:Status, "Not Started", Level:Level, "2.1", Level:Level, "3.1")

It returns a 0. and it should be 11.

Find more posts tagged with

Accepted answers

Paul Newcome

@Michele L Yes. It all depends on the data type in the column you are referencing. Numerical values don't have quotes but text values do.

All comments

Paul Newcome

That is because the COUNTIFS is basically an AND function. The way you have it written, it will only count rows where the level is both 2.1 and 3.1 at the same exact time (which is not possible. You will need an OR function tucked in like so:

=COUNTIFS(Status:Status, "Not Started", Level:Level, OR(@cell = "2.1", @cell = "3.1"))

Michele L

Hi Paul,

That did not work. =COUNTIFS(Status:Status, "Not Started", Level:Level, OR(@cell = "2.1", @cell = "3.1")) Still coming up 0.

Michele L

Ok, I got it to work. Seems i had to remove quotes.

=COUNTIFS(Level:Level, OR(@cell = 3.1, @cell = 2.1), Status:Status, "Not Started")

Paul Newcome

@Michele L Yes. It all depends on the data type in the column you are referencing. Numerical values don't have quotes but text values do.

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.