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
Best Answer
-
@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))
Answers
-
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
-
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")).
-
@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))
-
You are a life saver - thank you!
-
@Maya B Happy to help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!