Countif with OR referencing multiple sheets

Options

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!

Best Answer

  • Kendra Hillmer
    Kendra Hillmer ✭✭✭✭✭
    Answer ✓
    Options

    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!

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Options

    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!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • Kendra Hillmer
    Kendra Hillmer ✭✭✭✭✭
    Answer ✓
    Options

    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!

  • Kendra Hillmer
    Kendra Hillmer ✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!