Counting when record falls within a dynamically updating date range, additional matching criteria

F

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
233
Office Version
  1. 365
Platform
  1. Windows
I have a couple cells that have formulas to calculate the last day of this week and the last day of last week:

Cell D2 is =TODAY() + (7 - WEEKDAY(TODAY()))
Cell E2 is =D2-7

Then on a RAW Data worksheet, I have the below sample of data

LABELPKEYNUMSUMMARYPNAMECREATEDUPDATEDRESOLUTIONDATECURRENT STATUSCHANGE AUTHORCHANGE DATEFIELD CHANGEDOLDSTRINGNEWSTRING
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed4/28/2023 10:15 AMassigneeSmith, John
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed4/28/2023 10:15 AMassigneeSmith, JohnWilliams, Jesse
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed4/28/2023 10:15 AMassigneeWilliams, JesseSmith, John
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed4/28/2023 10:16 AMstatusReady to DevelopDevelopment
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed5/3/2023 3:50 PMstatusDevelopmentReady for Code Review
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed5/3/2023 3:50 PMassigneeSmith, John
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed5/9/2023 10:52 AMassigneeWilliams, Jesse
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed5/9/2023 1:08 PMstatusMergeMerge Done
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed5/9/2023 1:08 PMassigneeWilliams, Jesse
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed6/1/2023 7:57 AMpriorityCriticalExternal Block
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed6/1/2023 8:01 AMpriorityExternal BlockBlocker
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed6/26/2023 11:15 AMpriorityBlockerExternal Block
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed8/14/2023 10:25 AMstatusReady for ReleaseCompleted
E2CEDGE-13283Vehicle payments batch jobs in TWS analysis (GM and Ford)Story3/28/2022 1:23 PM3/15/2023 6:09 PMCancelled3/28/2022 1:23 PMassigneeWilliams, Tom
E2CEDGE-13283Vehicle payments batch jobs in TWS analysis (GM and Ford)Story3/28/2022 1:23 PM3/15/2023 6:09 PMCancelled4/4/2022 8:35 PMassigneeWilliams, Tom
E2CEDGE-13283Vehicle payments batch jobs in TWS analysis (GM and Ford)Story3/28/2022 1:23 PM3/15/2023 6:09 PMCancelled5/26/2022 10:16 AMstatusOpenCancelled
E2CEDGE-13293QUOTE DELETION batch jobs in TWS analysisStory3/29/2022 2:07 PM3/15/2023 6:09 PMOpen3/29/2022 2:07 PMassigneeWilliams, TomGibbs, Harry
E2CEDGE-13293QUOTE DELETION batch jobs in TWS analysisStory3/29/2022 2:07 PM3/15/2023 6:09 PMOpen3/30/2022 8:41 AMassigneeGibbs, Harry
E2CEDGE-13645CAN_UPD_LICENSEEpic5/9/2022 4:28 PM8/16/2023 8:45 AM8/16/2023 8:45 AMCancelled8/16/2023 8:45 AMstatusOpenCancelled
E2CEDGE-13900Data Update Batch JobsUser Story6/27/2022 2:50 PM#################11/8/2022 7:55 AMCancelled11/8/2022 7:55 AMstatusOpenCancelled
E2CEDGE-13901Learn Batch job scheduling in TWSStory6/27/2022 3:42 PM#################Cancelled6/28/2022 7:51 AMassigneePalmer, Arnold
E2CEDGE-13901Learn Batch job scheduling in TWSStory6/27/2022 3:42 PM#################Cancelled7/27/2022 12:51 PMstatusGathering RequirementsCancelled
E2CEDGE-13901Learn Batch job scheduling in TWSStory6/27/2022 3:42 PM#################Cancelled7/27/2022 12:52 PMstatusCancelledCancelled
E2CEDGE-13901Learn Batch job scheduling in TWSStory6/27/2022 3:42 PM#################Cancelled11/8/2022 7:55 AMstatusOpenCancelled
E2CEDGE-13904Define the different types of batch jobsStory6/28/2022 7:47 AM#################Cancelled6/28/2022 7:49 AMassigneeDoe, Jane
E2CEDGE-13904Define the different types of batch jobsStory6/28/2022 7:47 AM#################Cancelled6/28/2022 7:56 AMassigneeDoe, JaneDoe, John
E2CEDGE-13904Define the different types of batch jobsStory6/28/2022 7:47 AM#################Cancelled6/30/2022 7:30 AMstatusPre-GroomingCancelled
E2CEDGE-13905List migration options for each batch job typeStory6/28/2022 7:55 AM#################Cancelled7/6/2022 9:18 AMpriorityMajorExternal Block
E2CEDGE-13905List migration options for each batch job typeStory6/28/2022 7:55 AM#################Cancelled7/18/2022 8:03 AMpriorityExternal BlockMajor
E2CEDGE-13905List migration options for each batch job typeStory6/28/2022 7:55 AM#################Cancelled7/18/2022 12:55 PMstatusGathering RequirementsCancelled
E2CEDGE-13906Validate if the List of Batch jobs is completeStory6/28/2022 7:59 AM#################Cancelled11/8/2022 7:56 AMstatusOpenCancelled
E2CEDGE-13907Validate the count of Long running jobsStory6/28/2022 8:00 AM#################Cancelled7/8/2022 9:36 AMassigneeGibbs, Harry
E2CEDGE-13907Validate the count of Long running jobsStory6/28/2022 8:00 AM#################Cancelled7/19/2022 9:16 AMstatusGathering RequirementsCancelled
E2CEDGE-13907Validate the count of Long running jobsStory6/28/2022 8:00 AM#################Cancelled7/27/2022 10:53 AMassigneeGibbs, HarryWilliams, Tom
E2CEDGE-13908Identify common themes or patterns of Job failureStory6/28/2022 8:03 AM#################Cancelled6/29/2022 10:24 AMassigneeWilliams, Tom
E2CEDGE-13908Identify common themes or patterns of Job failureStory6/28/2022 8:03 AM#################Cancelled7/25/2022 11:00 AMstatusGathering RequirementsCancelled
E2CEDGE-13908Identify common themes or patterns of Job failureStory6/28/2022 8:03 AM#################Cancelled7/26/2022 1:33 PMstatusCancelledCancelled
E2CEDGE-13908Identify common themes or patterns of Job failureStory6/28/2022 8:03 AM#################Cancelled7/27/2022 10:41 AMstatusCancelledCancelled
E2CEDGE-13908Identify common themes or patterns of Job failureStory6/28/2022 8:03 AM#################Cancelled7/27/2022 12:57 PMstatusCancelledCancelled
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled6/29/2022 10:33 AMassigneeGibbs, Harry
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:08 PMstatusReady to DevelopDevelopment
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:08 PMstatusDevelopmentReady for Code Review
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:08 PMstatusMergeMerge Done
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:09 PMassigneeGibbs, Harry
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:28 PMassigneeGibbs, Harry
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:29 PMassigneeGibbs, Harry
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:29 PMstatusMerge DoneCancelled
E2CEDGE-13910Identify common actions or type of actions before a job runsStory6/28/2022 8:06 AM#################Cancelled7/5/2022 1:30 PMassigneeGibbs, Harry
E2CEDGE-13910Identify common actions or type of actions before a job runsStory6/28/2022 8:06 AM#################Cancelled7/11/2022 9:15 AMstatusGathering RequirementsCancelled
E2CEDGE-13911Identify techniques used by jobs in wait mode like pollingStory6/28/2022 8:09 AM#################Cancelled8/2/2022 12:56 PMstatusOpenCancelled
E2CEDGE-13952EDGE class structure analysisStory7/8/2022 10:34 AM#################Cancelled7/8/2022 10:35 AMassigneeDoe, John
E2CEDGE-13952EDGE class structure analysisStory7/8/2022 10:34 AM#################Cancelled7/18/2022 9:24 AMstatusGathering RequirementsCancelled

I'd like to Count the number of records on my Raw Data worksheet where Column K "Change Date" falls within my date range defined on my current worksheet cells D2 and E2 (noted at the beginning of this post) AND the record's value in Column M "Newstring" = "Merge Done"

Anyone know how to put that in a formula? Thanks in advance
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Maybe you can try something like this:
Excel Formula: 
=SUM(LET(CD,INT($K$5:$K$56),NS,$N$5:$N$56,(CD>=$E$2)*(NS="Merge Done")*(CD<=$D$2)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
Count # of values that fall within a certain date range
Replies
1
Views
382
RobP
R
F
  • Question
Looking to index some data based on multiple criteria, then index further data based on the 1st indexed data results
2
Replies
13
Views
498
KRice
K
F
  • Question
Would like to index different data points based on multiple criteria, and then index additional data in the same columns based on date/time proximity
Replies
4
Views
361
freeb1893
F
E
  • Solved
Lookup value from range when name matches AND date falls within date range
Replies
4
Views
692
Alex Blakenburg
A
F
  • Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
226
freeb1893
F

Forum statistics

Threads
1,215,069
Messages
6,122,954
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top