Sum formula using another sheet

I am trying to do a count of cases an investigator has completed between a date range (using a differen sheet)

If I do a

I have this formula in my Sheet summary and of course it works

=COUNTIFS(Investigator:Investigator, "Vanessa", [Disposition date]:[Disposition date], @cell >= DATE(2020, 8, 1), [Disposition date]:[Disposition date], @cell <= DATE(2020, 8, 31))

but when i try to do another sheet (so I can do a chart graph) i get an unparseable error usng the below formula

=COUNTIFS({CRU Range 1}, "Vanessa ), IF({CRU Range 2}, @cell >= DATE(2020, 8, 1), IF({CRU Range 2}, @cell <= DATE(2020, 8, 31))

I even tried removing the IF between the comma and the next range but still an error

any help would be greatly appreciated!

Find more posts tagged with

Accepted answers

All comments

David Joyeuse

Hi @lesmickin

I don't really get why you're using IF within a COUNTIFS function. You don't need them. Also, there's some typos within it.

Also, if I understand your formulas correctly, you seem to want to count the number of "Vanessa" during the Month of August. So we can also optimized this a bit.

1st one:

=COUNTIFS([Investigator]:[Investigator], "Vanessa", [Disposition Date]:[Disposition Date], MONTH(@cell)=8)

2nd one is this then:

=COUNTIFS({CRU Range 1}, "Vanessa", {CRU Range 2}, MONTH(@cell]=8)

Also, when you're working with other sheets range, it really helps to have a name that is meaning to the range. CRU Range 1 & 2 seems OK right now. But in 2 or 3 months you won't remember them. I would advise you to rename them with the name of the column at the very least.

Hope it helped!

lesmickin

So i used the formula, but still unparseable and or getting the invalid data type error for both formulas. I am assuming it's because of the month? does my date column in the other sheet need to be formatted differently?

Sheet summary I used this: =COUNTIFS(Investigator:Investigator, "Vanessa", [Disposition date]:[Disposition date], MONTH(@cell) = 8)

Paul Newcome

To clean up your second formula as is:

=COUNTIFS({CRU Range 1}, "Vanessa", {CRU Range 2}, @cell >= DATE(2020, 8, 1), {CRU Range 2}, @cell <= DATE(2020, 8, 31))

Organizing and consolidating:

=COUNTIFS({CRU Range 1}, "Vanessa", {CRU Range 2}, AND(@cell >= DATE(2020, 8, 1), @cell <= DATE(2020, 8, 31)))

Easier management for different months/years:

=COUNTIFS({CRU Range 1}, "Vanessa", {CRU Range 2}, AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2020))

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.