Countifs formula across sheets

Jeanette Conley

I am trying to count if the UBMD Dyad (department) on the 2nd sheet (screenshot provided) is any of the ones listed on the 1st sheet and in which month(date column is on the 2nd sheet) it was completed. I was given the formula listed on the 1st sheet but the result is 0. I am not sure what is missing in the formula.

• =COUNTIFS({UBMD Dyads from Source Sheet}, [UBMD Dyads]@row, {Date from Source Sheet}, AND(IFERROR(MONTH(@cell), 0) = 11, IFERROR(YEAR(@cell), 0) = 2021))

1st sheet

2nd sheet

Joseph Nabors

I think the "IFERROR" is messing it up and I'm not sure if it's needed. Try:

=COUNTIFS({UBMD Dyads from Source Sheet}, [UBMD Dyads]@row, {Date from Source Sheet}, AND(MONTH(@cell) = 11, YEAR(@cell) = 2021))

Jeanette Conley

Hi

I added the formula you provided and now I am receiving an invalid error

Paul Newcome

The IFERROR is needed.

Exactly how are the dates populated and have you double checked that they are in fact in a date type column?

Jeanette Conley

The dates are entered via a form and the column is setup by date type.

Paul Newcome

Ok. And how exactly is the UBMD Dyads column populated? Is it free text or dropdown?

Jeanette Conley

The UBMD Dyads on the reference sheet is dropdown

Paul Newcome

Ok. Try copying the list from the dropdown column properties and pasting it into the cells on the formula sheet.

Jeanette Conley

I copied the list from the drop down column and pasted it into the cells on the formula sheet but nothing changed

Paul Newcome

Hmm... The only thing I can think of that could be causing this then when be an issue with your ranges. Double check that they are both referencing the appropriate columns (single column each).

Jeanette Conley

I have checked and they are both referencing the appropriate column and now the result is back to 0

Paul Newcome

Are people selecting a date or are they manually typing in numbers?

Jeanette Conley

People are manually entering a date

Paul Newcome

Ok. Insert a temporary text/number column next to the date column. In it enter this column formula:

=IF(NOT(ISDATE(Date@row)), "Not A Date")

Are there any rows that are populated with "Not A Date" in this temporary column?

Jeanette Conley

I added the column and entered the formula and no rows populated with "Not a Date"

Paul Newcome

I just noticed that the formula posted and the formula in the screenshot do not match. You need to use the one with the @cell references inside of the MONTH and YEAR functions.