COUNTIF Across Multiple Sheets

brianw ✭✭✭
edited 03/24/22 in Formulas and Functions

Hi All

I am trying to get a count on total number of overdue items on a different sheet by project manager. I have tried multiple variations of the below formula but have yet to find one that works.

=COUNTIF(AND({Data Range 1}, CONTAINS("Carolyn Kuo", @cell), {Data Range 1} = "Carolyn Kuo"))

=COUNTIF({Data Range 1}, CONTAINS([Project Manager]@row, @cell)), AND({Data Range 3} = "Overdue")

=COUNTIFS({Data Range 3}, CONTAINS("Overdue", @cell), {Data Range 1}, CONTAINS("Carolyn Kuo"))

Basically, if the Project Manager Column on the sheet "Data" contains a specific project managers name and the status column on the sheet "Data" states Overdue I want it to count on a different sheet. I can get it to count the number of times a Project Managers name appears on the sheet "Data", but when I add the overdue component I get errors.

I dont feel like this should be that hard to do but I cannot seem to figure it out.



  • brianw
    brianw ✭✭✭

    Nevermind I figured it out

    =COUNTIFS({Data Range 1}, CONTAINS([Project Manager]@row, @cell), {Data Range 3}, CONTAINS("Overdue", @cell))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @brianw

    I hope you're well and safe!


    Glad that you figured it out!

    Be safe and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • brianw
    brianw ✭✭✭

    Thanks Andrée!

    I do have another question you may be able to assist with along these same lines. I want to pull the same data, but look at the assigned due dates and break the overdue items into columns by how long they have been overdue (i.e., <30 days, 30-60, 60-90, 90+). I am having trouble getting the date range to work. For the 30 days I am using this, which is returning a value, but not the correct one.

    =COUNTIFS({Data Range 1}, CONTAINS([Project Manager]@row, @cell), {Data Range 3}, CONTAINS("Overdue", @cell), {Data Range 1}, <TODAY(30))

    And then trying this for 30-60, but its returning no values, which isnt correct either.

    =COUNTIFS({Data Range 1}, CONTAINS([Project Manager]@row, @cell), {DataRange 3}, CONTAINS("Overdue", @cell), {Data Range 1}, >TODAY(30), {Data Range 1}, <TODAY(60))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!