I need to count the number of enquiries between the dates 01/01/2023 & 31/12/2023

Malky

I need to count the number of enquiries between the dates 01/01/2023 & 31/12/2023

Just can't get the formula right, keep getting Invalid or Unparsable, I know it should be straight forward but...currently trying to use COUNTIFS

I have the formula to count all the values that equal enquiry but can't set it to select between a certain date range.

florian.zbinden7

you should not have the "[" and "]".

I got it working :

=COUNTIFS({Design - Client Project Register Range 2}, "Enquiry", {Design - Client Project Register Range 3}, @cell >= DATE(2023, 1, 1), {Design - Client Project Register Range 3}, @cell <= DATE(2023, 12, 31))

florian.zbinden7

hi,

would this work ? seems to work for me, I get "3" as a result.

I have formatted the columns as dates (I have DD/MM/YY).

BR

Florian

Malky

Hi Florian, thanks for the reply...

This is the latest formula that I'm using

=COUNTIFS([{{Design - Client Project Register Range 2}}]:[{{Design - Client Project Register Range 2}}], "Enquiry", [{Design - Client Project Register Range 3}]:[{Design - Client Project Register Range 3}], >=01/01/2023, [{Design - Client Project Register Range 3}]:[{Design - Client Project Register Range 3}], <=12/31/2023)

Result, UNPARSEABLE 🤷 this suggestion came from ChatGPT

florian.zbinden7

I think you miss the "@cell" in front of your date and if you hardcode the date in the formula, then you should use "DATE()" I think, but test it first without it.

why do you have 2 "{" at the start ? this is a mistake I think, you need only one.

try this:

=COUNTIFS([{Design - Client Project Register Range 2}]:[{Design - Client Project Register Range 2}], "Enquiry", [{Design - Client Project Register Range 3}]:[{Design - Client Project Register Range 3}], @cell >= DATE(2023, 01, 01), [{Design - Client Project Register Range 3}]:[{Design - Client Project Register Range 3}], @cell <= DATE(2023, 12, 31))

Malky

Thanks again, still get UNPARSEABLE...

I'll check this again next week move onto something else I can solve for now

florian.zbinden7

you should not have the "[" and "]".

I got it working :

=COUNTIFS({Design - Client Project Register Range 2}, "Enquiry", {Design - Client Project Register Range 3}, @cell >= DATE(2023, 1, 1), {Design - Client Project Register Range 3}, @cell <= DATE(2023, 12, 31))

Malky

Nice one Florian, it works, filtered count confirms.

Many thanks...

florian.zbinden7

excellent, happy to help