Error with COUNTIFs and date formula

Michaela Kamenska

Hello!

Can someone please advise what am I doing wrong getting this error?

The formula used is as follows:

=COUNTIFS({ref sheet}, AND(MONTH(@cell) = 1, YEAR(@cell) = 2021))

The date column in the reference sheet is set as "Date" and the format is DD-Mon-YYYY (e.g. 19-Jan-2021). I've tried creating a default date column in my metrics sheet too, however, it made no change.

Any help is appreciated!

Bassam.M Khalil

Hi @Michaela Kamenska,

Hope you are fine, please try the following formula

=COUNTIFS({ref sheet}, IFERROR(YEAR(@cell), 0) = 2021, {ref sheet}, IFERROR(MONTH(@cell), 0) = 1)

Bassam.M Khalil

Hi @Michaela Kamenska,

Hope you are fine, please try the following formula

=COUNTIFS({ref sheet}, IFERROR(YEAR(@cell), 0) = 2021, {ref sheet}, IFERROR(MONTH(@cell), 0) = 1)

Michaela Kamenska

Hi @Bassam.M Khalil ,

Thank you for your response. I appreciate your solution, however, this is a dynamic sheet where many rows are added every day. Is there a solution where I can use the whole column as a ref?

Bassam.M Khalil

Hi @Michaela Kamenska

sorry i correct the formula for you please read my response again

Michaela Kamenska

Thank you very much, it works great!

Bassam.M Khalil

Happy to help you any time @Michaela Kamenska