# CountIFs using multiple criteria

Options
✭✭✭✭✭

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".

Jennifer

Tags:

• ✭✭✭✭✭✭
Options

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

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

• ✭✭✭✭✭✭
Options

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

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

• ✭✭✭✭✭✭
Options

@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.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

@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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!