Cross Sheet Formula, COUNTIFS + AND?

MichelleR

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!

AravindGP

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

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

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

MichelleR

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