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

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.

Best Answer

  • florian.zbinden7
    florian.zbinden7 ✭✭✭✭
    edited 01/26/24 Answer ✓

    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))

Answers

  • florian.zbinden7
    florian.zbinden7 ✭✭✭✭
    edited 01/26/24

    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

  • 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
    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))

  • Thanks again, still get UNPARSEABLE...

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

  • florian.zbinden7
    florian.zbinden7 ✭✭✭✭
    edited 01/26/24 Answer ✓

    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))

  • Nice one Florian, it works, filtered count confirms.

    Many thanks...

  • florian.zbinden7
    florian.zbinden7 ✭✭✭✭

    excellent, happy to help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!