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)

 

[Bandito1]

Hope this makes more clear.

In worksheet Dashboard, in column O it should count if there is an "x" somewhere in the line of the batch number.
If there is 1 "x" or more it should count for 1.

If there are no "x" in the row it should count as 0.

So in the sample below (where i only look in column N and S for "x") the number in dashboard under column O should be 6.
There are 6 rows with 1 "x" in it.
The first row has an "x" in N and an "x" in S but should be counted as 1.

At this moment the column O in dashboard shows the number 3. Seems like it only looks in column N..

Dashboard.xlsm
	B
1	2022
BMR data (2)

Dashboard.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Month							Retour M	Retour E	Retour P	Retour PS	Retour WH	Total returns	Retour M + E unique
BMR data (2)

Dashboard.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
14	December							3	4	3	3	0	13	3
BMR data (2)

Cell Formulas
Range	Formula
B14	B14	=DATE($B$1,ROWS($1:12),1)
I14	I14	=SUMPRODUCT(('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L>=B14)*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L<=EOMONTH(B14,0))*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$N:$N="x"))
J14	J14	=SUMPRODUCT(('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L>=B14)*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L<=EOMONTH(B14,0))*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$S:$S="x"))
K14	K14	=SUMPRODUCT(('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L>=B14)*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L<=EOMONTH(B14,0))*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$X:$X="x"))
L14	L14	=SUMPRODUCT(('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L>=B14)*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L<=EOMONTH(B14,0))*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$AC:$AC="x"))
M14	M14	=SUMPRODUCT(('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L>=B14)*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L<=EOMONTH(B14,0))*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$AH:$AH="x"))
N14	N14	=SUM(I14:M14)
O14	O14	=SUMPRODUCT(SIGN(('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L>=B14)*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$L:$L<=EOMONTH(B14,0)*('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$N:$N="x")+('[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$S:$S="x"))))

Tracker QA on the floor New.xlsm
	L		N	O	P	Q		S	T	U	V		X	Y	Z	AA		AC	AD	AE	AF		AH	AI	AJ	AK
723			Retour M					Retour E					Retour P					Return PS					Return WH
724			Retour	Date	Return	Date		Retour	Date	Return	Date		Retour	Date	Return	Date		Retour	date	Return	Date		Retour	Date	Return	Date
725	2-dec-22		x	01-dec-22	x	03-dec-22		x	30-nov-22	x	1-dec-22		x	30-nov-22	x	01-dec-22
726	1-dec-22																	x	30-nov-22	x	01-dec-22
727	4-dec-22							x	3-dec-22	x	4-dec-22		x	29-nov-22	x	30-nov-22		x	29-nov-22	x	30-nov-22
728	1-dec-22
729	1-dec-22
730	2-dec-22																						x	03-dec-22		5-dec-22
731	2-dec-22		x	01-dec-22	x	02-dec-22
732	2-dec-22
733	5-dec-22							x	3-dec-22	x	5-dec-22
734	5-dec-22																	x	04-dec-22	x	05-dec-22
735	3-dec-22		x	02-dec-22	x	02-dec-22
736	2-dec-22							x	2-dec-22	x	2-dec-22
737	4-dec-22
738	4-dec-22
739	4-dec-22
740	5-dec-22
741	5-dec-22
742	4-dec-22												x	04-dec-22	x	04-dec-22
Tracker QA on the Floor

 

[Fluff]

You have one of the brackets in the wrong place, it should be

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

 

[Bandito1]

Still wrong number come out
But i found the cause of it.
To bad no solution yet.

The source file is a big file (725 rows).
There are blanks in it and lots of other dates.

It seems like (i tested by deleting everything except the values december in column L) this is causing the wrong numbers.

How come and how can i solve?

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

This gives the right number and it doesn't matter the big size file and blanks and other stuff.

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

Can't handle the blanks and other dates in the big excell file
It should only check the "x" behind december dates in column L but it seems like it counts far more

 

[Fluff]

How about

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

 

[Bandito1]

Wauw that's it!
It works!

Thank you very much for your patients and time!

 

[Fluff]

Glad it's sorted & thanks for the feedback.