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?

Thanks,

Brian

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.