Data Extraction With OR Logic Including Dates

[NickFru]

Hi, I have two lists and want to have a formula that I can use as the COUNT of the records of two employee lists to see what names are in both then to use this count for data to extract relevant records. The formula for count I am using is =SUMPRODUCT(--ISNUMBER(MATCH(B2:B28,E2,E11,0)/COUNTIFS(A2:A28,">="&I2 (Start Date), A2:A28,"<="&J2 (End Date) but don't think this correct.

I finally need to enter a formula in the first cell under date of the empty grid, which I think maybe similar to IF(ROWS(E$17:E17)>COUNT,"",INDEX(Date Range), AGGREGATE(15,6,(ROW(Date Range)-ROW(A2)+1)/ISNUMBER(MATCH(Employee First Range (Column B), Employee Second Range (Column E),0)*(Date Range >=Start Date)*(Date Range<=End Date),ROWS(E$17:E17) but not sure on this. The info is below;

Date	Employee	Contacts Made
01/07/2024​	Nick	75​
08/07/2024​	Dave	80​
15/07/2024​	Helen	85​
22/07/2024​	Paul	90​
29/07/2024​	Tom	95​
05/08/2024​	James	100​
12/08/2024​	Alex	105​
19/08/2024​	Carolyn	110​
26/08/2024​	Nick	115​
02/09/2024​	Alex	120​
09/09/2024​	James	125​
16/09/2024​	Helen	130​
23/09/2024​	Nick	135​
30/09/2024​	James	140​

Employee

Gary

James

Terry

Fiona

Jason

Paula

Nick

Tim

Scott

Mandy

Count		Start Date	End Date
6​		01/07/2024​	30/09/2024​

Date	Employee	Contacts Made
Formula To Be Entered

Many thanks for all your help.

Nick

 

[StephenCrump]

The formula for count I am using is .... but don't think this correct.

I finally need to enter a formula ... which I think maybe similar to .... but not sure on this.

... So it's really not clear to us what you're trying to do, which would be why you've had no replies.

Can you please post your layout and the results you expect to see, preferably using XL2BB, so that we don't need to retype, and so that we can see the relevant row/column headers?

 

[NickFru]

... So it's really not clear to us what you're trying to do, which would be why you've had no replies.

Can you please post your layout and the results you expect to see, preferably using XL2BB, so that we don't need to retype, and so that we can see the relevant row/column headers?

Hi Stephen, apologies if I have confused you from my question in my previous thread and hopefully the explanation below will provide a bit more clarity.

The essence of what I am trying to achieve is to extract data records with OR criteria from a single column. So, I have an original report with a list of dates with employee names and number of contacts they have made. In a completely separate column, I have a new list of employees names and what I am trying to do is to ask the question, "Is the employee name in the first list equal to any of the names in the new separate employee list" and do these fall between the dates specified when we have a start date of the report and and end date of the report.

The original information is below;

Date	Employee	Contacts Made
01/07/2024​	Nick	75​
08/07/2024​	Dave	80​
15/07/2024​	Helen	85​
22/07/2024​	Paul	90​
29/07/2024​	Tom	95​
05/08/2024​	James	100​
12/08/2024​	Alex	105​
19/08/2024​	Carolyn	110​
26/08/2024​	Nick	115​
02/09/2024​	Alex	120​
09/09/2024​	James	125​
16/09/2024​	Helen	130​
23/09/2024​	Nick	135​
30/09/2024​	James	140​

On a separate list, these employees have now been provided to me;

Employee

Gary

James

Terry

Fiona

Jason

Paula

Nick

Tim

Scott

Mandy

Start Date End Date
01/07/2024 30/09/2024

So in order for me to get the correct COUNT of records, I have tried the formula, SUMPRODUCT(Date Range From Original List>=Start Date)*(Date Range From Original List<=End Date)--ISNUMBER(MATCH(Employee Range On Original List),(Employee Range On New List),0)) but this does not appear to be working.

Finally, if the correct COUNT can be ascertained, I would like to be able to put in a data extract formula based on the following and then this can be copied across.

Date	Employee	Contacts Made
=IF(ROWS(A$2:A2)>COUNT,"",AGGREGATE(15,6,ROW(A:A)-ROW(A2)+1)/(Date Range From Original List>=Start Date)*(Date Range From Original List<=End Date), ISNUMBER(MATCH(Employee Range On Original List, Employee Name On New List,0),ROWS(A$2:A2)

I hope the above illustration is clear and once again, apologies for any confusion caused on my part.

Nick

 

[StephenCrump]

I have corrected your count formula (i.e. the SUMPRODUCT) below.

It's not clear why you want to use an AGGREGATE formula construction.

My guess all along is that a simple SUMIFS() will produce the required results. If that's not correct, please let us know the results you're expecting, which you still haven't told us.

	A	B	C
1	Date	Employee	Contact
2	1 Jul 2024	Nick	75
3	8 Jul 2024	Dave	80
4	15 Jul 2024	Helen	85
5	22 Jul 2024	Paul	90
6	29 Jul 2024	Tom	95
7	5 Aug 2024	James	100
8	12 Aug 2024	Alex	105
9	19 Aug 2024	Carolyn	110
10	26 Aug 2024	Nick	115
11	2 Sep 2024	Alex	120
12	9 Sep 2024	James	125
13	16 Sep 2024	Helen	130
14	23 Sep 2024	Nick	135
15	30 Sep 2024	James	140
16
17	StartDate	1 Jul 2024
18	EndDate	30 Sep 2024
19	Count	6
20
21	List2
22	Gary	-
23	James	365
24	Terry	-
25	Fiona	-
26	Jason	-
27	Paula	-
28	Nick	325
29	Tim	-
30	Scott	-
31	Mandy	-
32
Sheet1

Cell Formulas
Range	Formula
B19	B19	=SUMPRODUCT((A2:A15>=StartDate)*(A2:A15<=EndDate)*ISNUMBER(MATCH(B2:B15,A22:A31,)))
B22:B31	B22	=IFERROR(1/(1/SUMIFS(C$2:C$15,A$2:A$15,">="&StartDate,A$2:A$15,"<="&EndDate,B$2:B$15,A22)),"-")

Named Ranges
Name	Refers To	Cells
EndDate	=Sheet8!$B$18	B19, B22:B31
StartDate	=Sheet8!$B$17	B19, B22:B31

 

[NickFru]

I have corrected your count formula (i.e. the SUMPRODUCT) below.

It's not clear why you want to use an AGGREGATE formula construction.

My guess all along is that a simple SUMIFS() will produce the required results. If that's not correct, please let us know the results you're expecting, which you still haven't told us.

	A	B	C
1	Date	Employee	Contact
2	1 Jul 2024	Nick	75
3	8 Jul 2024	Dave	80
4	15 Jul 2024	Helen	85
5	22 Jul 2024	Paul	90
6	29 Jul 2024	Tom	95
7	5 Aug 2024	James	100
8	12 Aug 2024	Alex	105
9	19 Aug 2024	Carolyn	110
10	26 Aug 2024	Nick	115
11	2 Sep 2024	Alex	120
12	9 Sep 2024	James	125
13	16 Sep 2024	Helen	130
14	23 Sep 2024	Nick	135
15	30 Sep 2024	James	140
16
17	StartDate	1 Jul 2024
18	EndDate	30 Sep 2024
19	Count	6
20
21	List2
22	Gary	-
23	James	365
24	Terry	-
25	Fiona	-
26	Jason	-
27	Paula	-
28	Nick	325
29	Tim	-
30	Scott	-
31	Mandy	-
32
Sheet1

Cell Formulas
Range	Formula
B19	B19	=SUMPRODUCT((A2:A15>=StartDate)*(A2:A15<=EndDate)*ISNUMBER(MATCH(B2:B15,A22:A31,)))
B22:B31	B22	=IFERROR(1/(1/SUMIFS(C$2:C$15,A$2:A$15,">="&StartDate,A$2:A$15,"<="&EndDate,B$2:B$15,A22)),"-")

Named Ranges
Name	Refers To	Cells
EndDate	=Sheet8!$B$18	B19, B22:B31
StartDate	=Sheet8!$B$17	B19, B22:B31

Hi Stephen, your first formula being the SUMPRODUCT formula is great, it works to produce the correct COUNT of records.

I didn't need to use the SUMIFS formula as you have stated above as I am purely extracting the relevant records of name, employee and contacts made which is equal to the COUNT of those records (ie if there is one name that is on both lists and that name appears on the main list say 2 times within the date range criteria, it will extract the date, name and contacts made of 2 records, this is the reason for using AGGREGATE but I could have used the SMALL function to get the nth number of times within the criteria.

I have now inserted part of the COUNT formula into the following formula and it works perfectly;

Starting my formula in cell A18 on my worksheet =IF(ROWS(A$18:A18)>COUNT,"",INDEX(AGGREGATE(15,6,(ROW(A:A)-ROW(A2)+1)/ISNUMBER(MATCH(A2:A15,E2:E11,0)*(Date Range>=Start Date)*(Date Range<=End Date)), ROWS(A$18:A18). This then copied across into B18 & C18 and if there are only 2 records (the equal of the COUNT), it will just show these only.

Thanks again for your help.

Nick

 

[StephenCrump]

OK, thanks, that makes sense. I'm glad you got it working.