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!
Best 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 GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Answers

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

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 GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

@AravindGP Thank you!! I overcomplicated the situation, the AND was totally unnecessary :)
Help Article Resources
Categories
Check out the Formula Handbook template!