Hi, I am trying to add two COUNTIFS from different sheets and using the formula below. =COUNTIFS({Engineer Live Rota}, $Name@row , {Date Live Rota}, >=DATE(2020, 2, 1), {Date Live Rota}, =DATE(2020, 2, 1), {Archived Rota Range 2}, <=DATE(2021, 1, 31))) Does this formula look as though it should work? Individually they work, but together I get the #INVALID OPERATION message. Thanks

Adding two COUNTIFS

Hi,

I am trying to add two COUNTIFS from different sheets and using the formula below.

=COUNTIFS({Engineer Live Rota}, $Name@row , {Date Live Rota}, >=DATE(2020, 2, 1), {Date Live Rota}, <=DATE(2021, 1, 31) + COUNTIFS({Archived - Rota Range 1}, $Name@row , {Archived - Rota Range 2}, >=DATE(2020, 2, 1), {Archived Rota Range 2}, <=DATE(2021, 1, 31)))

Does this formula look as though it should work? Individually they work, but together I get the #INVALID OPERATION message.

Thanks

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

It is just a misplaced closing parenthesis. Try this:

=COUNTIFS({Engineer Live Rota}, $Name@row, {Date Live Rota}, >=DATE(2020, 2, 1), {Date Live Rota}, <=DATE(2021, 1, 31)) + COUNTIFS({Archived - Rota Range 1}, $Name@row, {Archived - Rota Range 2}, >=DATE(2020, 2, 1), {Archived Rota Range 2}, <=DATE(2021, 1, 31))

All comments

Paul Newcome

It is just a misplaced closing parenthesis. Try this:

Richard King

Thanks Paul, that sorted it!

Paul Newcome

Happy to help. 👍️

Richard King

Hi Paul,

Just a slight variation on the previous formula where I want to check if a another cell is not blank, now I get #UNPARSEABLE.

Any thought on the formula below?

=COUNTIFS({Engineer Live Rota}, $Name@row , {Date Live Rota}, >=DATE(2020, 2, 1), {Date Live Rota}, <=DATE(2021, 1, 31), {Covering Eng Live Rota}, NOT(ISBLANK(@row )) + COUNTIFS({Archived - Callout Rota Range 1}, $Name@row , {Archived - Callout Rota Range 2}, >=DATE(2020, 2, 1), {Archived - Callout Rota Range 2}, <=DATE(2021, 1, 31), {Archived - Callout Rota Range 3}, NOT(ISBLANK(@row ))

Thanks

Paul Newcome

Your parenthesis are off again. Give this a try instead:

=COUNTIFS({Engineer Live Rota}, $Name@row, {Date Live Rota}, >=DATE(2020, 2, 1), {Date Live Rota}, <=DATE(2021, 1, 31), {Covering Eng Live Rota}, @cell <> "") + COUNTIFS({Archived - Callout Rota Range 1}, $Name@row, {Archived - Callout Rota Range 2}, >=DATE(2020, 2, 1), {Archived - Callout Rota Range 2}, <=DATE(2021, 1, 31), {Archived - Callout Rota Range 3}, @cell <> "")

Richard King

Thanks again Paul, saved me a lot trial an error.

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.