Joining multiple COUNTIFS statements

Brian Murphy

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

 

Nick Burrus

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!

Paul Newcome

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

 

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

Carlos Figueroa

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

Tina Davis

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

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?

Thanks!

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.