Looking for formula help on a date range plus one more item
Good Morning. Looking for help from folks that are far more savy than me.
I have a schedule that I am trying to link to a metrics sheet so I can then report to a dashboard.
Example below.
I am trying to get the metrics sheet to calculate everything that contains "Real Estate Search Begin" and is in 2024. Would have a similar formula for different years.
I got the "Real Estate Search Begin" part to work but I can't figure out all the pieces I need to collect the date range.
It is referencing a different sheet. Works for one but struggling with the different pieces (comma's, spaces, (, {, etc) to make it all work.
Using a COUNTIF function at the moment.
Any help would be appreciated!
Best Answer

Hello @chrisakins01
Without Column or Range Names, I've used some generic ones but you can replace them with what you have.
Try this on your metrics sheet
=MAX(COUNTIFS({Real Estate Search Begin Range}, CONTAINS("Real Estate Search Begin", @cell), {Start Date Range}, YEAR(@cell) = 2024), COUNTIFS({Real Estate Search Begin Range}, CONTAINS("Real Estate Search Begin", @cell), {End Date Range}, YEAR(@cell) = 2024))
Just to break it down for you, COUNTIFS allows for more than 1 criteria. So first, range1 = This will be the range that contains your "Real Estate Search Begin" cells, and the criterion1 ,CONTAINS("Real Estate Search Begin", @cell), will count how many cells contain that exact Quote within the range. range2 = Your Start Date Range, and criterion2 is the year you're looking at 2024. This can be changed for your other years as needed.
I repeat the same process but my range2 this time is comparing the End Date Range to see if that falls within 2024.
As you can see in my example below, there are 4 Lines that meet the first COUNTIFS criterion, while there are 5 that meet the 2nd COUNTIFS criterion. So now I just find the MAX of the two numbers, and as you can see there are 5 lines that contain "Real Estate Search Begin" and falls sometime in 2024.
Hope this helps!
Davin Vo  Sevan Technology
Smartsheet Platinum Partner
Answers

Hello chrisakins01,
Did you want just a count of all the times "Real Estate Search Begin (DFWF)" shows up in the year 2024 or did you want the actual data to come back as well?

Just the amount of time's it shows up in 2024. Or other years where applicable.
There would be 100+ of those different tasks so trying to capture the data by year.

Hello @chrisakins01
Without Column or Range Names, I've used some generic ones but you can replace them with what you have.
Try this on your metrics sheet
=MAX(COUNTIFS({Real Estate Search Begin Range}, CONTAINS("Real Estate Search Begin", @cell), {Start Date Range}, YEAR(@cell) = 2024), COUNTIFS({Real Estate Search Begin Range}, CONTAINS("Real Estate Search Begin", @cell), {End Date Range}, YEAR(@cell) = 2024))
Just to break it down for you, COUNTIFS allows for more than 1 criteria. So first, range1 = This will be the range that contains your "Real Estate Search Begin" cells, and the criterion1 ,CONTAINS("Real Estate Search Begin", @cell), will count how many cells contain that exact Quote within the range. range2 = Your Start Date Range, and criterion2 is the year you're looking at 2024. This can be changed for your other years as needed.
I repeat the same process but my range2 this time is comparing the End Date Range to see if that falls within 2024.
As you can see in my example below, there are 4 Lines that meet the first COUNTIFS criterion, while there are 5 that meet the 2nd COUNTIFS criterion. So now I just find the MAX of the two numbers, and as you can see there are 5 lines that contain "Real Estate Search Begin" and falls sometime in 2024.
Hope this helps!
Davin Vo  Sevan Technology
Smartsheet Platinum Partner

Davin VoThat did the trick! Thanks so much. Greatlly appreciate it.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!