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.

Find more posts tagged with

Formulas

Reports

Accepted answers

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

All comments

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

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 :

Malky

Nice one Florian, it works, filtered count confirms.

Many thanks...

florian.zbinden7

excellent, happy to help

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.