Countifs in Sheet Summary Not correct Result

Michele L

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.

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.

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.