Countifs after a date

I am trying to count the number of closed opportunities for the 2021 calendar year that meet the criteria of: Won AND is of market sector offices new. I have used the formula below but it's not working. Where have I gone wrong?

=COUNTIFS({Sheet - Sales Pipeline Sales Stage}, "Closed Won", {Sheet - Closed Opps Market Sector}, "OFF_OFF Offices New", AND{Perth Closed Opportunity Submission Due Date}, >= (1 / 1 / 21))

Find more posts tagged with

Formulas

Accepted answers

Lidiya S.

Good morning @Sarah Dawson ,

Your formula looks well put together, the only hiccups I see are the AND in the last section and the way you show the date. The COUNTIF formula will automatically look for items that meet all of the criteria, no need for the AND. Additionally, if you do use AND in the future, just double check that you are using parenthesis around the function.

For using specific dates in a formula try:

=DATE(2021, 1, 1)

Here is your formula with corrections, let me know if this helps!

=COUNTIFS({Sheet - Sales Pipeline Sales Stage}, "Closed Won", {Sheet - Closed Opps Market Sector}, "OFF_OFF Offices New", {Perth Closed Opportunity Submission Due Date}, >=DATE(2021, 1, 1) )

Best,

Lidiya Shutaya

lidiya@ddbconsultants.ca

All comments

Lidiya S.

Good morning @Sarah Dawson ,

For using specific dates in a formula try:

=DATE(2021, 1, 1)

Here is your formula with corrections, let me know if this helps!

=COUNTIFS({Sheet - Sales Pipeline Sales Stage}, "Closed Won", {Sheet - Closed Opps Market Sector}, "OFF_OFF Offices New", {Perth Closed Opportunity Submission Due Date}, >=DATE(2021, 1, 1) )

Best,

Lidiya Shutaya

lidiya@ddbconsultants.ca

Sarah Dawson

That worked great. Thanks for your help.

Lidiya S.

@Sarah Dawson Glad it worked! Let me know if you have any other questions :)

Lidiya Shutaya

lidiya@ddbconsultants.ca

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.