Formula and Function : COUNTIFS : Invalid Data Type (Cross Sheet Referencing)

Marlana K.

I am working on some key metrics for one of our teams that track referrals. I am getting an Invalid Data Type and I know it is due to "blank cells" in two of the columns I am looking at. ISBLANK is not my strong area and unfortunately I cannot generate the formula using the AI Tools because it is not capable of cross sheet referencing. I have tried creating a column in the sheet and using the AI but I still get Invalid Data Type.

Columns in the Sheet are:

GG. Prodigy Profile Created Employee Full Name (this column has blank cells)

Year (Date of Outcome) 2023

FF. Date Prodigy Profile Confirmed as Created (this column has blank cells)

Current formula in my Metric sheet is

COUNTIFS({OBH CM Referral Tracker GG Prodigy Profile Created}, "INSERT NAME of EACH PERSON", {OBH CM Referral Tracker Year}, "2023", {OBH CM Referral Tracker FF)}, "12")

The formulas I tried in the actual sheet using AI are:

COUNTIFS([GG. Prodigy Profile Created Employee Full Name]:[GG. Prodigy Profile Created Employee Full Name], "Rachel Gegner", [Year (Date of Outcome)]:[Year (Date of Outcome)], 2023, MONTH([FF. Date Prodigy Profile Confirmed as Created]:[FF. Date Prodigy Profile Confirmed as Created]), 12)

COUNTIFS(NOT(ISBLANK(MONTH([FF. Date Prodigy Profile Confirmed as Created]:[FF. Date Prodigy Profile Confirmed as Created]), 12, [GG. Prodigy Profile Created Employee Full Name]:[GG. Prodigy Profile Created Employee Full Name], "Rachel Gegner", [Year (Date of Outcome)]:[Year (Date of Outcome)], 2023)))

AI Generated and the count is incorrect for the person I tested they have 2 the number comes back as 0

COUNTIFS([FF. Date Prodigy Profile Confirmed as Created]:[FF. Date Prodigy Profile Confirmed as Created], <>"", [FF. Date Prodigy Profile Confirmed as Created]:[FF. Date Prodigy Profile Confirmed as Created], 12, [GG. Prodigy Profile Created Employee Full Name]:[GG. Prodigy Profile Created Employee Full Name], <>"", [GG. Prodigy Profile Created Employee Full Name]:[GG. Prodigy Profile Created Employee Full Name], "Rachel Gegner", [Year (Date of Outcome)]:[Year (Date of Outcome)], 2023)

Any suggestions or help would be greatly appreciated.

Carson Penticuff

This assumes {OBH CM Referral Tracker Year} is formatted as text/number and {OBH CM Referral Tracker FF)} is formatted as date. Is this correct?

=COUNTIFS({OBH CM Referral Tracker GG Prodigy Profile Created}, "INSERT NAME of EACH PERSON", {OBH CM Referral Tracker Year}, 2023, {OBH CM Referral Tracker FF)}, IFERROR(MONTH(@cell), 0) = 12)

Carson Penticuff

This assumes {OBH CM Referral Tracker Year} is formatted as text/number and {OBH CM Referral Tracker FF)} is formatted as date. Is this correct?

=COUNTIFS({OBH CM Referral Tracker GG Prodigy Profile Created}, "INSERT NAME of EACH PERSON", {OBH CM Referral Tracker Year}, 2023, {OBH CM Referral Tracker FF)}, IFERROR(MONTH(@cell), 0) = 12)

Marlana K.

@Carson Penticuff Your assumption is correct.

THANK YOU IT WORKED APPRECIATE THE ASSIST!!!!

COUNTIFS({OBH CM Referral Tracker GG Prodigy Profile Created}, "Rxxxxx Gxxxxx", {OBH CM Referral Tracker Year Date of Outcome}, 2023, {OBH CM Referral Tracker 12-2023 FF}, IFERROR(MONTH(@cell), 0) = 12)

Carson Penticuff

Happy to help!

Marlana K.

@Carson Penticuff I take back the that worked. Confirmed with the team and they are still saying numbers don't match. Looking at the report format they are correct it gives me a different number than the metric sheet which generates the graphs.

The report filter is FF. Date Prodigy Profile Confirmed is between (first day of month / last day of month)

Grouped it by GG Prodigy Profile Created Employee Full Name

Carson Penticuff

Is it possible that some of your source data may not be formatted consistently with their respective columns? You could try the following formulas individually to verify the results are as you would expect. Dates are especially susceptible to appearing to be correct while truly be treated as text.

=COUNTIFS({OBH CM Referral Tracker GG Prodigy Profile Created}, "INSERT NAME of EACH PERSON")

=COUNTIFS({OBH CM Referral Tracker Year}, 2023)

=COUNTIFS({OBH CM Referral Tracker FF)}, ISDATE(@cell))

Marlana K.

@Carson Penticuff

Thank you. Those all work as expected. I think I know what is causing the discrepancy, we have multiple columns with dates. When gathering information from the team that had entered the intake request for a dashboard containing charts and metrics they wanted the year pulled from column W. Date of Outcome. I created additional columns for reporting in the actual sheet one labeled Year (Date of Outcome) which contains the formula =IFERROR(YEAR([W. Date of Final Outcome]@row), "unknown"). The years don't match!!!!!

Updated my formula to use COUNTIFS({OBH CM Referral Tracker GG Prodigy Profile Created}, "Px Txxx", {OBH CM Referral Tracker FF}, IFERROR(MONTH(@cell), 0) = 1)

Now the numbers match her report. I can even go back to my original formula removing the Year (Date of Outcome) and it appears the numbers are matching what she has.

Unfortunately I did not design the sheet they are using so I am at the mercy of their data and the way it is entered. Which when I began this process I really poked some big holes in that process and they had to review and change how they were entering data which still seems to be a problem!