SUMPRODUCT with condition

[virtuosok]

Hi, appreciate if you can help...
I have a range with dates, which also contains some duplicates and blank cells. To find the # of unique entries in this range I'm using the following formula (example):
=SUMPRODUCT((J2:J10<>"")/COUNTIF(J2:J10,J2:J10&""))
This works. However.
How do I find the # of unique entries (or, to be more specific, unique dates) in column J if the output should also be matching one more criteria? Let's say, all unique entries in column J must also have a matching entry "001" in column K, while column K would be filled with random entries 001, 002, 003, 004 etc.

 

[C Moore]

In cells L2:L10, cell L2, =IF(L2=matchvalue,J2,""). Then change your original equation from Js to Ls.

 

[virtuosok]

Apologies I may not be getting this. Where does the condition from column K come into play?
Also, cell L3 would have =IF(L3=matchvalue,J3,"") etc?

 

[Eric W]

How about:

Book1
	J	K	L	M
1				Uniques
2	1/1/2020	1		3
3	1/1/2020	1
4	2/1/2020	1
5	1/2/2020	2
6	1/1/2020	3
7	1/2/2020	4
8
9
10	3/1/2020	1
Sheet6

Cell Formulas
Range	Formula
M2	M2	=SUM(SIGN(FREQUENCY(IF(K2:K10=1,J2:J10),J2:J10)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[virtuosok]

THANKS

 

[C Moore]

Apologies I may not be getting this. Where does the condition from column K come into play?
Also, cell L3 would have =IF(L3=matchvalue,J3,"") etc?

Sorry about that, L2 should've been K2 (L3 to K3 in your mentioned formula.
=IF(K2=matchvalue,J2,"")
This returns all your rows that meet the single criteria you mentioned. Then pulls out the uniques from column L.