Identify rows within date range with criteria

[sligh]

I have a history table that tracks changes to project records. Im having problems creating formula that can indicating earliest history record with the stage of "Opportunity".

My end goal is to create columns things like "total days within a particular stage" and columns to help determine how many project records were in a particular stage during each month out of the year.

History ID	Project ID	Record Name	Stage	Date
1	1	Alpha Project	Prospect	5/20/2017
2	1	Alpha Project	Opportunity	10/21/2017
3	2	Beta Project	Opportunity	7/4/2017
4	3	Gamma Project	Prospect	9/1/2016
5	4	Delta Project	Opportunity	4/6/2017

[Vraag en antwoord]

Use a pivottable or place an example file.

[sligh]

Forum wants me to post more before I can attach files.

Please see the following. paste in browser:
bit.ly/2LylfZJ

Here is a sample table with some of the other formula that I'm using.

[FlameRetired]

Is this something like you had in mind?

This formula in H2 to return the earliest date.

=MIN($E$2:$E$100)

Then the first of each following month in H3 and filled down.

=EDATE(H2,1)

Then in I2 filled across and down to get the counts.

=COUNTIFS($D$2:$D$100,I$1,$E$2:$E$100,">"&EOMONTH($H2,-1),$E$2:$E$100,"<="&EOMONTH($H2,0))

	A	B	C	D	E	F	G	H	I	J
1	History ID	Project ID	Record Name	Stage	Date				Prospect	Opportunity
2	1	1	Alpha Project	Prospect	5/20/2017			9/1/2016	1	0
3	2	1	Alpha Project	Opportunity	10/21/2017			10/1/2016	0	0
4	3	2	Beta Project	Opportunity	7/4/2017			11/1/2016	0	0
5	4	3	Gamma Project	Prospect	9/1/2016			12/1/2016	0	0
6	5	4	Delta Project	Opportunity	4/6/2017			1/1/2017	0	0
7								2/1/2017	0	0
8								3/1/2017	0	0
9								4/1/2017	0	1
10								5/1/2017	1	0
11								6/1/2017	0	0
12								7/1/2017	0	1
13								8/1/2017	0	0
14								9/1/2017	0	0
15								10/1/2017	0	1
16								11/1/2017	0	0
17								12/1/2017	0	0
18								1/1/2018	0	0
19								2/1/2018	0	0

[sligh]

Each history record may or may not have a stage change. What I'm looking for is if Project Alpha changes to "Opportunity" on 3/1/2017 and then to a different stage on 6/6/2017, it will be counted as an active "Opportunity" during March, April, May, and June.

I need to find a way to determine how many projects are in the opportunity stage during a given month.

If I understand your formula correct, it only accounts for if there is a history record with that stage during a given month but it does not account for the months between. Am I mistaken?

[FlameRetired]

If I understand your formula correct, it only accounts for if there is a history record with that stage during a given month but it does not account for the months between. Am I mistaken?

No you aren’t mistaken.

Are you OK with helper columns? May we take liberties with the layout of the source data? May we sort the source data?

I will probably have some more questions. I need to rethink this one.

In the meantime what method did you use in the attempt to upload? If you used the paperclip icon that hasn't worked for a while. Try it this way and see if the forum still won't let you upload.

To attach a representative file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.