What is wrong with this formula?

Hi,

I'm trying to count all consultation meetings between 1/1/19-12/31/21. This is the formula I came up with and it does not work.

=COUNTIFS([RPP Consultation Meeting?]:[RPP Consultation Meeting?], HAS(@cell, "TRUE"), [Intake Date]:[Intake Date] >= DATE(2019, 1, 1), [Intake Date]:[Intake Date] <= DATE(2021, 12, 31))

Any help would be very much appreciated!

Thanks

Find more posts tagged with

Formulas

Accepted answers

Heather Duff

@Maya B Eureka! We were missing some commas. And you'll want to change "TRUE" to 1.

=COUNTIFS([RPP Consultation Meeting?]:[RPP Consultation Meeting?], 1, [Intake Date]:[Intake Date], >=DATE(2019, 1, 1), [Intake Date]:[Intake Date], <=DATE(2021, 12, 31))

All comments

Heather Duff

Hi @Maya B ,

Is the RPP Consultation Meeting? column a multi-select dropdown or single select/text? HAS is only usable within a multi-select dropdown; otherwise, you should just be able to use:

=COUNTIFS([RPP Consultation Meeting?]:[RPP Consultation Meeting?], "TRUE", [Intake Date]:[Intake Date] >= DATE(2019, 1, 1), [Intake Date]:[Intake Date] <= DATE(2021, 12, 31))

Also, be sure your Intake Date column is set as a date column within column properties.

Hope this helps! Let me kn

Maya B

Hi Heather,

I tried using your updated formula, after making sure that the Intake Date column is set as a date column and I still get an INVALID OPERATION. Can you see anything else that might be wrong here?

Thanks!

P.S.

The RPP Consultation Meeting? column is a checkbox column. I am using the HAS formula for this column in another sheet summary function and it's working fine: =COUNTIF([RPP Consultation Meeting?]:[RPP Consultation Meeting?], HAS(@cell, "TRUE")).

Heather Duff

@Maya B Eureka! We were missing some commas. And you'll want to change "TRUE" to 1.

=COUNTIFS([RPP Consultation Meeting?]:[RPP Consultation Meeting?], 1, [Intake Date]:[Intake Date], >=DATE(2019, 1, 1), [Intake Date]:[Intake Date], <=DATE(2021, 12, 31))

Maya B

You are a life saver - thank you!

Heather Duff

@Maya B 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.