How to return count based on 3 conditions that must be true (two in the same column)?

Zach C

I'm looking to return the Total Count for all rows for the 1001 course that specifically contain an "Incomplete" or "Completed" Completion Status. This value will be the denominator in my 1001 Completion % metric.

Note: I'm not interested in the "Completed in Pilot" rows but still want those on the back end.

I have the below formula but it's returning "0"…

=COUNTIFS({Data Range 3}, AND(CONTAINS("1001", {Data Range 3}), CONTAINS("Completed", {Data Range 4}), CONTAINS("Incomplete", {Data Range 4})))

Any help would be appreciated!

KPH

Your COUNTIFS syntax is a little off. You need pairs of ranges and criteria. It looks like Data Range 3 is the Course range and you have the criteria for that mixed up with the range and criteria for the other column.

If you want to count rows that are "Completed" or "Incomplete" you should use OR rather than AND. AND will return only rows that match both criteria, which looks impossible from the data snippet. OR will return rows that match on either thing.

I have mocked up an example here without cross sheet references. This corrects the syntax and logic issues.

=COUNTIFS(Course:Course, "1001", [Completion Status]:[Completion Status], OR((CONTAINS("Completed", @cell)), CONTAINS("Incomplete", @cell)))

However, there may be a further problem. I don't see an example of Completed in your data snippet. If the "Completed" rows just have the word "Completed" (like I put in my example) and you do not want to include "Completed in Pilot" then the count will be wrong (the formula above returns 5). CONTAINS will count all the rows that contain the text - which "Completed in Pilot" and "Completed" both do.

So you might want to swap CONTAINS "Completed" for something else.

You can use doesn't contain Pilot

=COUNTIFS(Course:Course, "1001", [Completion Status]:[Completion Status], OR(NOT(CONTAINS("Pilot", @cell)), CONTAINS("Incomplete", @cell)))

Or you could use the count of Completed and add the count that contains Incomplete"

=COUNTIFS(Course:Course, "1001", [Completion Status]:[Completion Status], "Completed") + COUNTIFS(Course:Course, "1001", [Completion Status]:[Completion Status], CONTAINS("Incomplete", @cell))

This part will depend on what other values you have in your data but hopefully now you have the structure that will be easy.

kchenoweth

Try this:

=COUNT(COLLECT({Data Range 3},{Data Range 3},1001,{Data Range 4},"Incomplete",{Data Range 4},"Complete"))

Jgorsich

You are specifying that it should only count things that are course 1001 AND completed AND incomplete - no row is both complete and incomplete, therefore 0. Nest your completed and incomplete criteria inside of an "or()" wrapper and you might be good. Alternatively, count your completes and then add a count of your incompletes:

=countifs(course:course,1001,status:status,"completed")+countifs(course:course,1001,status:status,"incomplete")

Zach C

@Jgorsich makes sense. thank you!!

KPH

Your COUNTIFS syntax is a little off. You need pairs of ranges and criteria. It looks like Data Range 3 is the Course range and you have the criteria for that mixed up with the range and criteria for the other column.

If you want to count rows that are "Completed" or "Incomplete" you should use OR rather than AND. AND will return only rows that match both criteria, which looks impossible from the data snippet. OR will return rows that match on either thing.

I have mocked up an example here without cross sheet references. This corrects the syntax and logic issues.

=COUNTIFS(Course:Course, "1001", [Completion Status]:[Completion Status], OR((CONTAINS("Completed", @cell)), CONTAINS("Incomplete", @cell)))

However, there may be a further problem. I don't see an example of Completed in your data snippet. If the "Completed" rows just have the word "Completed" (like I put in my example) and you do not want to include "Completed in Pilot" then the count will be wrong (the formula above returns 5). CONTAINS will count all the rows that contain the text - which "Completed in Pilot" and "Completed" both do.

So you might want to swap CONTAINS "Completed" for something else.

You can use doesn't contain Pilot

=COUNTIFS(Course:Course, "1001", [Completion Status]:[Completion Status], OR(NOT(CONTAINS("Pilot", @cell)), CONTAINS("Incomplete", @cell)))

Or you could use the count of Completed and add the count that contains Incomplete"

=COUNTIFS(Course:Course, "1001", [Completion Status]:[Completion Status], "Completed") + COUNTIFS(Course:Course, "1001", [Completion Status]:[Completion Status], CONTAINS("Incomplete", @cell))

This part will depend on what other values you have in your data but hopefully now you have the structure that will be easy.

Zach C

@kchenoweth I wasn't able to get that to work but thanks for the suggestion!

Zach C

@KPH thanks for the detailed info! I was able to get that last one to work: =COUNTIFS(Course:Course, "1001", [Completion Status]:[Completion Status], "Completed") + COUNTIFS(Course:Course, "1001", [Completion Status]:[Completion Status], CONTAINS("Incomplete", @cell)). I think this one helps simplify instead of having to filter out any statuses containing "Pilot" in the text.

KPH

Great news. That last formula counts rows where the Completion Status exactly matches "Completed" and the Course is exactly "1001" and adds to it the count of rows where the Completion Status contains "Incomplete" and the Course is exactly "1001".

If you look at your data and find things that go against that logic you can adapt the logic using NOT and OR from the other examples. You have a toolkit of functions at your disposal 😀 Happy math-ing.