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!

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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:

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)

AravindGP

Hi @MichelleR

MichelleR

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.