Looking for formula help on a date range plus one more item

chrisakins01

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!

Davin Vo

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!

RDRGSJ00

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?

chrisakins01

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.

Davin Vo

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!

chrisakins01

Davin Vo-That did the trick! Thanks so much. Greatlly appreciate it.