Cross Sheet Formula, COUNTIFS + AND?

Hi!!!

Tried this and it is #UNPARSEABLE:

=COUNTIFS({cost savings check}, 1), AND({OPENED}, >=DATE(2023, 1, 1), {OPENED}, <=DATE(2023, 12, 31))

I've been noodling with it but can't seem to get it to work, help would be greatly appreciated!

Tags:

Best Answer

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @MichelleR


    Are you trying to get a count if the cost savings check is checked and the date is between 1st January to 31st December 2023? The formula should be =COUNTIFS({cost savings check}, 1, {OPENED}, >=DATE(2023,1,1),{OPENED},<=DATE(2023,12,31))

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

Answers

  • Jaime M.
    Jaime M. ✭✭✭

    Hey Michelle,

    It looks like you want the field to display the count if, a) cost savings = "1" and b) if the opened date is 1/1/2023, and c) the opened date is less than 12/31/2023

    You shouldn't need the "AND" in your equation, as "COUNTIFS" already assumes multiple requirements need to be met.

    Two ways you could easily do this using this equation:

    1. Define the dates you want to search between into your sheet as shown below. This way, you can reference these fields in your formula - just make sure to lock the reference to each with the dollar signs. In the snip below the "Opened" column is a Date column. I added the assumption that you want the cost savings count if the date is greater than or equal to 1/1/2023 or less than or equal to 12/31/2023. I created this equation in the sheet summary. (Shown in first snip) =COUNTIFS([Cost Savings]:[Cost Savings], 1, Opened:Opened, >=$Opened$1, Opened:Opened, <=$Opened$2)
    2. Alternatively, you could create a separate sheet that references your data sheet that has the cost savings and opened dates - with this you would not need to add the "Start Date 2023" and "End Date 2023" into your data sheet, and you could put the equation into a different column instead of the sheet summary. You would need to use the "Reference Another Sheet" button in this case to reference your data sheet. (Shown in the last 3 snips) =COUNTIFS({Test - Cost Savings}, 1, {Test - Opened}, >=[Start Date]@row, {Test - Opened}, <=[End Date]@row)


  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @MichelleR


    Are you trying to get a count if the cost savings check is checked and the date is between 1st January to 31st December 2023? The formula should be =COUNTIFS({cost savings check}, 1, {OPENED}, >=DATE(2023,1,1),{OPENED},<=DATE(2023,12,31))

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • @AravindGP Thank you!! I overcomplicated the situation, the AND was totally unnecessary :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!