Count Unique Values Based on Multiple Criteria

[BrettOlbrys1]

Table of Data

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H	Column I
EMPLOYEE	CUSTOMER NAME	DATE	QTY	TYPE	EMPLOYEE CODE 1	EMPLOYEE CODE 2	EMPLOYEE CODE 3
Angie	Joe's Crab Shack	4/1/24	2	Validated	e12345	e37892	e43876
Grace	Tea With Tracy	4/13/24	5	Validated	e63524	e98735	e65326
Brett	Bob the Builder	4/19/24	6	Not Validated	e93764	e36873	e67590
James	Tim's Outhouse	4/22/24	3	Validated	e18943	e15235	e28464
James	Tim's Outhouse	4/22/24	7	Not Validated	e18943	e15235	e28464
Brett	Bob the Builder	5/18/24	8	Validated	e93764	e36873	e67590
Angie	Joe's Crab Shack	6/3/24	1	Not Validated	e12345	e37892	e43876
James	Tim's Outhouse	6/30/24	9	Validated	e18943	e15235	e28464

- Every employee (column A) has three associated employee codes (columns F - H). NOTE: 1,000+ employees
- I have a unique list of employee codes that keyed an order. NOTE: 500,000+ orders

I want a formula that COUNTS the number of UNIQUE ORDERS based on the following criteria:

- between 4/1/24 and 6/30/24
- TYPE = Validated
- matches any of their three employee codes from columns F, G, or H

The output would be the list of employee names and the # of unique orders.

Thanks

 

[Cubist]

If I understand your ask correctly, try...

Book1
	A	B	C	D	E	F	G	H
1	EMPLOYEE	CUSTOMER NAME	DATE	QTY	TYPE	EMPLOYEE CODE 1	EMPLOYEE CODE 2	EMPLOYEE CODE 3
2	Angie	Joe's Crab Shack	4/1/2024	2	Validated	e12345	e37892	e43876
3	Grace	Tea With Tracy	4/13/2024	5	Validated	e63524	e98735	e65326
4	Brett	Bob the Builder	4/19/2024	6	Not Validated	e93764	e36873	e67590
5	James	Tim's Outhouse	4/22/2024	3	Validated	e18943	e15235	e28464
6	James	Tim's Outhouse	4/22/2024	7	Not Validated	e18943	e15235	e28464
7	Brett	Bob the Builder	5/18/2024	8	Validated	e93764	e36873	e67590
8	Angie	Joe's Crab Shack	6/3/2024	1	Not Validated	e12345	e37892	e43876
9	James	Tim's Outhouse	6/30/2024	9	Validated	e18943	e15235	e28464
10
11
12	Result
13	e12345	1
14	e15235	2
15	e18943	2
16	e28464	2
17	e36873	1
18	e37892	1
19	e43876	1
20	e63524	1
21	e65326	1
22	e67590	1
23	e93764	1
24	e98735	1
Sheet6

Cell Formulas
Range	Formula
B13:B24	B13	=SUM(($F$2:$H$9=A13)*($E$2:$E$9="Validated")*($C$2:$C$9>=DATE(2024,4,1))*($C$2:$C$9<=DATE(2024,6,30)))

 

[BrettOlbrys1]

Cubist, thanks for your reply, but not exactly. I should have added more detail to what I am asking.

-------------------------------

The list of employee codes that submitted an order is:

e12345
e65326
e36873
e18943
e93764
e43876
e15235

And the output should be:

Angie - 1
Grace - 1
Brett - 1
James - 2

The explanation for these counts is:

Angie - row 2 falls between the dates, is validated, and the employee code used matches one of her codes (column F)
Grace - same explanation as Angie, except Grace's code is in column H
Brett - row 4 does not count because it is "Not Validated," but row 7 counts because all criteria met
James - rows 5 and 9 count because both meet all criteria, but row 6 does not

Does that help clarify what I am asking?

 

[Cubist]

How about this?

Book1
	A	B	C	D	E	F	G	H
1	EMPLOYEE	CUSTOMER NAME	DATE	QTY	TYPE	EMPLOYEE CODE 1	EMPLOYEE CODE 2	EMPLOYEE CODE 3
2	Angie	Joe's Crab Shack	45383	2	Validated	e12345	e37892	e43876
3	Grace	Tea With Tracy	45395	5	Validated	e63524	e98735	e65326
4	Brett	Bob the Builder	45401	6	Not Validated	e93764	e36873	e67590
5	James	Tim's Outhouse	45404	3	Validated	e18943	e15235	e28464
6	James	Tim's Outhouse	45404	7	Not Validated	e18943	e15235	e28464
7	Brett	Bob the Builder	45430	8	Validated	e93764	e36873	e67590
8	Angie	Joe's Crab Shack	45446	1	Not Validated	e12345	e37892	e43876
9	James	Tim's Outhouse	45473	9	Validated	e18943	e15235	e28464
10
11
12	Order list	Employee	Count
13	e12345	Angie	1
14	e65326	Grace	1
15	e36873	Brett	1
16	e18943	James	2
17	e93764
18	e43876
19	e15235
Sheet3

Cell Formulas
Range	Formula
B13:B16	B13	=UNIQUE(A2:A9)
C13:C16	C13	=SUM( BYROW( --ISNUMBER(XMATCH(FILTER($F$2:$H$9,($A$2:$A$9=B13)*($E$2:$E$9="Validated")*($C$2:$C$9>=DATE(2024,4,1))*($C$2:$C$9<=DATE(2024,6,30))),$A$13:$A$19)), LAMBDA(r,--(SUM(r)>0))) )
Dynamic array formulas.

 

[BrettOlbrys1]

Cubist, thanks again. I believe I messed up when setting up my example and did not explain it correctly, so hopefully you are willing to help once again, and this should be the last time.

I have two data tables ("EMPLOYEE TABLE" and "ORDER TABLE") and the "OUTPUT TABLE"

The EMPLOYEE TABLE is setup like this:

EMPLOYEE NAME	EMPLOYEE ID	EMPLOYEE CODE 1	EMPLOYEE CODE 2	EMPLOYEE CODE 3
Angie	Ang12345	e12345	e37892	e43876
Brett	Bre12345	e93764	e36873	e67590
Grace	Gra12345	e63524	e98735	e65326
James	Jam12345	e18943	e15235	e28464

The ORDER TABLE is setup like this:

CUSTOMER NAME	DATE	EMPLOYEE CODE USED	QTY	TYPE
Joe's Crab Shack	4/1/24	e12345	2	Validated
Tea With Tracy	4/13/24	e67590	4	Validated
Bob the Builder	4/19/24	e65326	5	Not Validated
Tim's Outhouse	4/22/24	e18943	1	Validated
Tim's Outhouse	4/22/24	e15235	2	No Validated
Bob the Builder	5/18/24	e65326	3	Validated
Joe's Crab Shack	6/3/24	e37892	3	Not Validated
Tim's Outhouse	6/30/24	e15235	3	Validated

The OUTPUT TABLE is setup like this:

EMPLOYEE ID	ORDER COUNT
Ang12345	? - result should be 1
Bre12345	? - result should be 1
Gra12345	? - result should be 1
Jam12345	? - result should be 2

I want a formula that COUNTS the number of ORDERS (from the ORDER TABLE) for each employee based on the following criteria:

- between 4/1/24 and 6/30/24
- TYPE = Validated
- matches any of their three employee codes from the EMPLOYEE TABLE

 

[Cubist]

Try:

Book1
	A	B	C	D	E
1	EMPLOYEE NAME	EMPLOYEE ID	EMPLOYEE CODE 1	EMPLOYEE CODE 2	EMPLOYEE CODE 3
2	Angie	Ang12345	e12345	e37892	e43876
3	Brett	Bre12345	e93764	e36873	e67590
4	Grace	Gra12345	e63524	e98735	e65326
5	James	Jam12345	e18943	e15235	e28464
6
7	CUSTOMER NAME	DATE	EMPLOYEE CODE USED	QTY	TYPE
8	Joe's Crab Shack	4/1/24	e12345	2	Validated
9	Tea With Tracy	4/13/24	e67590	4	Validated
10	Bob the Builder	4/19/24	e65326	5	Not Validated
11	Tim's Outhouse	4/22/24	e18943	1	Validated
12	Tim's Outhouse	4/22/24	e15235	2	No Validated
13	Bob the Builder	5/18/24	e65326	3	Validated
14	Joe's Crab Shack	6/3/24	e37892	3	Not Validated
15	Tim's Outhouse	6/30/24	e15235	3	Validated
16
17	4/1/24	6/30/24
18	EMPLOYEE ID	ORDER COUNT
19	Ang12345	1
20	Bre12345	1
21	Gra12345	1
22	Jam12345	2
Sheet6

Cell Formulas
Range	Formula
B19:B22	B19	=SUM(COUNTIFS($C$8:$C$15,FILTER($C$2:$E$5,$B$2:$B$5=A19),$E$8:$E$15,"Validated",$B$8:$B$15,">="&$A$17,$B$8:$B$15,"<="&$B$17))

 

[BrettOlbrys1]

Thanks again!