# Joining multiple COUNTIFS statements

edited 12/09/19

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?

Thanks,

Brian

Tags:

• ✭✭✭✭✭✭

Hello,

Would you mind sending me a copy of your sheet to NBurrus @ Stria.com? 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.

• ✭✭✭✭✭✭

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

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

• edited 11/05/20

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

• ✭✭✭

• ✭✭✭✭✭

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?

Thanks!

• ✭✭✭✭✭

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!