Countif with OR referencing multiple sheets

I would like to count the number of times a status is "Closed" on three different sheets. It can say close on either 3 of the sheets - just want to bring back the total on a summary sheet.

Here is what I have so far but think my OR is misplaced:

=COUNTIFS(OR({Sheet 1}, "Closed"), OR({Sheet 2}, "Closed"), OR({Sheet 3}, "Closed"))))

Thank you for your help!

Find more posts tagged with

Accepted answers

Kendra Hillmer

Thank you so much!

If I ever had to take it one step further and add more criteria per each sheet - the status is Closed and the assigned to is Kendra - would it look like this?

=COUNTIFS({Sheet 1_Status}, "Closed", {Sheet 1_Assignee}, "Kendra", )+COUNTIFS({Sheet 2_Status}, "Closed", {Sheet 2_Assignee}, "Kendra")+COUNTIFS({Sheet 3_Status}, "Closed", {Sheet 3_Assignee}, "Kendra")

Thanks again so much!

All comments

Brett Wyrick

Hey Kendra!

Thanks for posting your formula as text and not as a screenshot, that helps a lot!

I don't believe you need the OR function for what you're wanting. You just want a total # of "Closed" from each range, right?

If so, use this:

=COUNTIFS({Sheet 1}, "Closed")+COUNTIFS({Sheet 2}, "Closed")+COUNTIFS({Sheet 3}, "Closed")

Let me know if that works!

Kendra Hillmer

Thank you so much!

If I ever had to take it one step further and add more criteria per each sheet - the status is Closed and the assigned to is Kendra - would it look like this?

Thanks again so much!

Kendra Hillmer

I just tried it and it worked!! Thank you again SO much!! I really appreciate your help!

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.