$ Absolute Date in Formula

Options

Hi all,


I have tried to create a formula as below, for a COUNTIFS the name is [Column X] 1, and the date is [Date Column] 1, but it is not working. Can anyone advise please?

=COUNTIFS({Date Received}, =(DATE, Date@row), {Case Owner}, $[Column X]$1)

Also tried:

=COUNTIFS({Date Received}, =(DATE, [Date] 4), {Case Owner}, $[Column X]$1)

Its coming back as Unparseable... It does work if I manually type the date, but that involves lots of work...


Thanks

Meg

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/15/21
    Options

    Hi @megan.griffiths

    Hope you are fine, please try the following formula.

    =COUNTIFS({Date Received}, Date@row, {Case Owner}, [Column X]@row)

    {Date Received} is the range of date in the reference sheet

    Date@row is the criteria for the date you counting

    {Case Owner} is the range of Case Owner in the reference sheet

    [Column X]@rowis the criteria for the Case Owner you counting

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • megan.griffiths
    megan.griffiths ✭✭✭✭
    Options

    Hi Bassam,


    Its now coming back as circular reference. Any idea how to solve that?


    Thanks,


    Meg

  • megan.griffiths
    megan.griffiths ✭✭✭✭
    Options

    Sorry, ignore me, it works! Thank you very much. Now, in order to drag down on the date column, I've tried making it an absolute, but its not working again, its staying at the old reference site:

    =COUNTIFS({Date Received}, $[Date Field]$20, {Case Owner}, $[Column X]$16)

    Any ideas?

    Thanks,

    Meg

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!