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

Tags:

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    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

  • Heather Duff
    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
    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
    Heather Duff ✭✭✭✭✭✭
    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))

  • Maya B
    Maya B ✭✭✭

    You are a life saver - thank you!

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!