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!


  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    edited 08/21/20

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