CountIFs using multiple criteria

JSpears

I want a count of the number of tasks that are complete. I don't want to include anything marked as not applicable or any headings (Hierarchy=Grandparent or parent), and it is counted as complete whether it completed by Medhost or by facility.

=COUNTIFS([Not Applicable]:[Not Applicable], 0, Hierarchy:Hierarchy, "Child", OR([Completed by MEDHOST]:[Completed by MEDHOST], 1, [Completed by Facility]:[Completed by Facility], 1))

The above formula is giving me "Invalid data type".

thank you in advance!

Jennifer

Paul Newcome

You will have to write two separate COUNTIFS (one for each completed column) and then add them together.

=COUNTIFS(.......) + COUNTIFS(.......)

Paul Newcome

You will have to write two separate COUNTIFS (one for each completed column) and then add them together.

=COUNTIFS(.......) + COUNTIFS(.......)

Darren Mullen

@JSpears @Paul Newcome is correct, but watch for double counting if both of your OR conditions are checked. I made a video to help illustrate how to do this.

https://youtu.be/y-m1fbdmgU8

Paul Newcome

If it is a possibility that both boxes could be checked on the same row and you want to avoid a double count, it is as easy as creating a 3rd COUNTIFS that counts rows where both are checked and subtracting it from the previous set of COUNTIFS.

=COUNTIFS(first column) + COUNTIFS(second column) - COUNTIFS(both columns)

Paul Newcome

@Darren Mullen This is not a case where the OR function comes into play because we are evaluating two different ranges as opposed to the same range with two different criteria.