Find unique rows with sumproduct

[Bandito1]

Hello all,

I got 2 sheets (data(1) and data(2)

Data(1) gathers data from data(2)

It keeps track of how many files are returned.

Now i would like to see unique returns.
Since total returns count the total.

Batch 3 in data(2) has been retoured 2 times but it should count for only 1.
If only one x is in the line it should count 1 and not sum up the x.

Can't manage to get it working since now it shows "1" at unique returns while it should show "2"

Dashboard_test.xlsm
	B	C	D	E	F
1	2022
2	Month	Retour A	Retour B	Total returns	Unique returns
3	January
4	February
5	March
6	April
7	May
8	June
9	July
10	August
11	September
12	October	2	1	3	1
13	November
14	December
15	Total	2	1
data(1)

Cell Formulas
Range	Formula
C12	C12	=SUMPRODUCT(('data(2)'!$B:$B>=B12)*('data(2)'!$B:$B<=EOMONTH(B12,0))*('data(2)'!$C:$C="x"))
D12	D12	=SUMPRODUCT(('data(2)'!$B:$B>=B12)*('data(2)'!$B:$B<=EOMONTH(B12,0))*('data(2)'!$H:$H="x"))
E12	E12	=SUM(C12:D12)
F12	F12	=SUMPRODUCT(('data(2)'!$B:$B>=B12)*('data(2)'!$B:$B<=EOMONTH(B12,0))*('data(2)'!$C:$C="x")*('data(2)'!$H:$H="x"))
B3:B14	B3	=DATE($B$1,ROWS($1:1),1)
C15:D15	C15	=SUM(C3:C14)

Dashboard_test.xlsm
	A	B	C	D	E	F		H
3	Batch		Retour A		Return A			Retour B
4	1	14-okt-22
5	2	4-okt-22	x
6	3	4-okt-22	x					x
data(2)

 

[Fluff]

How about

=SUMPRODUCT(SIGN(('Data(2)'!$B:$B>=B12)*('Data(2)'!$B:$B<=EOMONTH(B12,0))*('Data(2)'!$C:$C="x")+('Data(2)'!$H:$H="x")))

 

[Bandito1]

No, not exactly.

Now it says unique returns is 3 while it should be 2
Batch 1 has no returns (since no x under Retour A or Retour B)
Batch 2 has 1 return
Batch 3 has 2 retours but it should count as 1 as we looking for unique.

 

[Fluff]

Now it says unique returns is 3

Not for me it doesn't

Fluff.xlsm
	A	B	C	D	E	F
1		2022
2		Month	Retour A	Retour B	Total returns	Unique returns
3		01/01/2022
4		01/02/2022
5		01/03/2022
6		01/04/2022
7		01/05/2022
8		01/06/2022
9		01/07/2022
10		01/08/2022
11		01/09/2022
12		01/10/2022			0	2
13		01/11/2022
14		01/12/2022
15		Total
Details

Cell Formulas
Range	Formula
E12	E12	=SUM(C12:D12)
F12	F12	=SUMPRODUCT(SIGN(('Data(2)'!$B:$B>=B12)*('Data(2)'!$B:$B<=EOMONTH(B12,0))*('Data(2)'!$C:$C="x")+('Data(2)'!$H:$H="x")))
B3:B14	B3	=DATE($B$1,ROWS($1:1),1)

 

[Bandito1]

Now it does for me too
Now i try to expand it, thank you very much

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Bandito1]

Hate this,

It works in my test files, but when i want to use it in my "final" file it doesn't.

Maybe you can see the mistake i make?

=SUMPRODUCT(SIGN(('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L>=B12)*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L<=EOMONTH(B12;0)*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$S:$S="x")+('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$N:$N="x"))))

It doesn't count; the result is 1 while it should be 2

Tracker QA on the floor New.xlsm
	N	O	P	Q		S	T	U
12	x	05-dec-22
15						x	5-dec-22
Tracker QA on the Floor

 

[Fluff]

Check that the dates in col L are correct & real dates not text.

 

[Bandito1]

All my other formules work with column L like totals and count only in column N the "x" or only in column S the "x"
Only when i try to find unique (so 1 or more "x" in a line) it doesn't work right.

Can't be that the mistake is in column L is right?

 

[Fluff]

Without being able to see the actual data & the other formula you use it's impossible to say.