How to find specific text within a string, in a COUNTIFS statement
(This was posted as a question previously, however I am unable to mark that as "unanswered" and am asking this again)
Hi everyone,
I am using a COUNTIFS to search a linked sheet, with one of the conditional IFS to be if the date falls in a specific month, and a specific year.
Example: Condition 1, Condition 2, Condition 3 = Must have "Jan" in referenced dates, Condition 4 = Year must be "2022" in referenced dates
Have tried "*"&"jan"&"*" as both an IF, and as part of a nested FIND statement - both to no success.
A previous posts incorrect answer proposed this:
COUNTIFS(
{Studio Follow-On Projects Status (Prod) Range 1}, Jurisdiction@row,
{Studio Follow-On Projects Status (Prod) Range 2}, <>"DONE",
{Studio Follow-On Projects Status (Prod) Range 2}, <>"CANCELLED",
{Studio Follow-On Projects Status (Prod) Range 4},
IFERROR(MONTH(@cell),0)=4)
Any other tips please?
Answers
-
Hi Rudy,
Please share a screenshot of your sheet with column headings.
The basic structure of your formula for the month of January and year 2021 will be:
AND({Condition 1}, IFERROR(MONTH(@cell),0)=1, {Condition 2}, IFERROR(MONTH(@cell),0)=1, {Condition 3}, IFERROR(MONTH(@cell),0)=1, {Condition 4}, IFERROR(year(@cell),0)="2021")
Your condition ranges need to point to single date columns.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark,
Thank you for your input.
Can I share the sheets with you to take a look?
Cheers,
Rudy
-
I am attempting to count the number of titles by Release and by Date 3, from the sheet below (Test Project Status).
The query is to count these into the sheet below, based on those conditions.
Not sure how to make the IFERROR statement work in this context.
-
Are you entering the dates into a date type column or are they in a text/number column?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!