Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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!

Best Answer

  • ✭✭✭✭✭
    Answer ✓

    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

  • ✭✭✭✭✭

    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!

  • ✭✭✭✭✭
    Answer ✓

    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!

  • ✭✭✭✭✭

    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!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6