What is wrong with this formula?

Maya B

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

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

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!