$ Absolute Date in Formula

megan.griffiths

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

Bassam Khalil

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

megan.griffiths

Hi Bassam,

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

Thanks,

Meg

megan.griffiths

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