Sum formula using another sheet

lesmickin

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!

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))