Countifs, Sum Product and Unique Entries

[rex759]

Hello,

The formula counts the number of occurrences if the IDs are Unique and falls between certain dates. I also want to include the sales person into the mix. In the below example, the results should be one since Bill has one unique ID that falls between the date range.

I tried adding the reference to the formula but could not get it to work.

Any help is appreciated.

Book1
	A	B	C	D	E	F	G
1		ID	Sales Assoc	Date	4/1/17	4/30/17	Bill
2		1471490339	Bill	4/11/17
3		1471490339	Bill	4/11/17		2
4		1471490339	Bill	4/11/17
5		1471731669	Rick	4/26/17
6		1471731669	Rick	4/26/17
7		1473630635	Bill	10/20/17
8
Sheet3

Cell Formulas
Range	Formula
F3	{=SUMPRODUCT(IF((D2:D8<=F1)*(D2:D8>=E1), 1/COUNTIFS(D2:D8, "<="&F1, D2:D8, ">="&E1, B2:B8, B2:B8),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​
1​	ID​	Sales Assoc​	Date​	4/1/2017​	4/30/2017​	Bill
2​	1471490339​	Bill	4/11/2017​
3​	1471490339​	Bill	4/11/2017​		1​
4​	1471490339​	Bill	4/11/2017​
5​	1471731669​	Rick	4/26/2017​
6​	1471731669​	Rick	4/26/2017​
7​	1473630635​	Bill	10/20/2017​

<tbody>
</tbody>

e3=SUM(IF(FREQUENCY(IF(C2:C7>=D1,IF(C2:C7<=E1,IF(B2:B7=F1,MATCH(B2:B7,B2:B7,0)))),ROW(B2:B7)-ROW(B2)+1),1))

control+shift+enter

 

[rex759]

Hello,

Thanks for responding.

I made one change to your formula and it worked for me.

e3=SUM(IF(FREQUENCY(IF(C2:C7>=D1,IF(C2:C7<=E1,IF(B2:B7=F1,MATCH(A2:A7,A2:A7,0)))),ROW(B2:B7)-ROW(B2)+1),1))

control+shift+enter

Thanks again