Joining multiple COUNTIFS statements

Hi there.  I'm trying to create a single formula that will count items in a column on sheet A if they fall between two dates and also count items on sheet B if they fall between two dates.  The item name is the same in both sheets and the date ranges are the same in both sheets.  The formula I have (which does not work) looks like this:

=COUNTIFS({2019_05 Range 1}, "DedicatedInternetAccess", {2019_05 Range 3}, >DATE(2019, 3, 31), {2019_05 Range 3}, <DATE(2019, 5, 1), ({Active Product Orders Range 2}, "DedicatedInternetAccess", {Active Product Orders Range 3}, >DATE(2019, 3, 31), {Active Product Orders Range 3}, <DATE(2019, 5, 1))).

So, this portion of the formula works: =COUNTIFS({2019_05 Range 1}, "DedicatedInternetAccess", {2019_05 Range 3}, >DATE(2019, 3, 31), {2019_05 Range 3}, <DATE(2019, 5, 1))

And this portion of the formula works: =COUNTIFS({Active Product Orders Range 2}, "DedicatedInternetAccess", {Active Product Orders Range 3}, >DATE(2019, 3, 31), {Active Product Orders Range 3}, <DATE(2019, 5, 1))

But when I attempt to put them together in one string, it fails.  What am I missing?






  • Nick Burrus
    Nick Burrus ✭✭✭✭✭✭


    Would you mind sending me a copy of your sheet to NBurrus @ I'm happy to take a look at it and see if I can come up with a solution for you!

    Dr. St Nicholas Burrus DHA, PMP

    I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You need to take the two working formulas and add them together.


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

  • Carlos Figueroa
    edited 11/05/20

    "=COUNTIFS(.......................) + COUNTIFS(...................)" does not work for me. I keep getting #Invalid Operation

  • Tina Davis
    Tina Davis ✭✭✭

    Was this ever resolved as I am having the same challenge?

  • Josh Reed
    Josh Reed ✭✭✭✭✭

    I am having a similar issue, it works sometimes

    =COUNTIFS({R&R Status}, "Unassigned", {Group}, "North America", {TA}, "Managed Care") + COUNTIFS({R&R Status}, "Not Started", {Group}, "North America", {TA}, "Managed Care") + COUNTIFS({R&R Status}, "Awaiting Documents", {Group}, "North America", {TA}, "Managed Care")

    Simple function, I just want to count multiple specific criteria. Sometimes it works and sometimes it says #Invalid Operation. This is frustrating because I'm attempting to make a workflow dashboard and list the counts of how many projects are a certain status, for a given therapeutic area.

    Does anyone know a workaround that is more concise? Utilizing helper sheets or something?


  • Josh Reed
    Josh Reed ✭✭✭✭✭

    Correction: after saving the sheet, closing and reloading it all of the functions now work. I am however still interested to find out if anyone has come up with a more direct, less muddy, way to accomplish the same task.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!