Error with COUNTIFs and date formula

01/19/21
Accepted

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!

Popular Tags:

Best Answer

  • Bassam.M KhalilBassam.M Khalil ✭✭✭✭✭
    edited 01/19/21 Accepted Answer

    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)

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

Answers

  • Bassam.M KhalilBassam.M Khalil ✭✭✭✭✭
    edited 01/19/21 Accepted Answer

    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)

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

  • 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 KhalilBassam.M Khalil ✭✭✭✭✭
    edited 01/19/21

    Hi @Michaela Kamenska

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

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

  • Thank you very much, it works great!

  • Bassam.M KhalilBassam.M Khalil ✭✭✭✭✭

    Happy to help you any time @Michaela Kamenska

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

Sign In or Register to comment.