Count if unique value + empty cell with four criteria

[Jescanilla]

Good afternoon. as the title said im trying to count if unique values and even empty cells with for different criteria such as year, week and so on, i was trying to use
=SUM(IF(FREQUENCY(IF(LEN($J:$J)>0;MATCH($J:$J;$J:$J;0);"");IF(LEN($J:$J)>0;MATCH($J:$J;$J:$J;0);""))>0;1))
but i couldn't manage to add the criteria's, i paste an example sheet of the result that i must have (but i did it manual just for explanation purposes). Please help me add criteria on that formula or build a better one.

Libro KPI mantenimiento (version 3).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
5	year	week	truck	type	ID
6	1	3	605	mc	asdasd
7	1	3	605	mc	123
8	1	3	605	mc	456mc67		year	week	truck	type	sum
9	1	3	605	mc	456mc67		1	3	605	mc	5	6
10	1	3	605	mc	456mc67		2	4	605	mc	2
11	1	3	605	mc
12	1	3	605	mc	asd
13	1	3	605	mc	asd
14	2	4	605	mc	asddd
15	2	4	605	mc
16	2	4	605	mp
17	2	4	605	mp
18	2	4	605	mp
19	2	4	605	mp
Sheet4

Cell Formulas
Range	Formula
L9	L9	=SUM(IF(FREQUENCY(IF(LEN($E:$E)>0,MATCH($E:$E,$E:$E,0),""),IF(LEN($E:$E)>0,MATCH($E:$E,$E:$E,0),""))>0,1))

Thanks by advance.

 

[etaf]

not sure i follow , i count 8 not 5 or 6

whats the empty cells ???

using unique() to pull out the list and then a countifs()

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	year	week	truck	type	ID
2	1	3	605	mc	asdasd
3	1	3	605	mc	123
4	1	3	605	mc	456mc67		year	week	truck	type	sum
5	1	3	605	mc	456mc67		1	3	605	mc	5	6
6	1	3	605	mc	456mc67		2	4	605	mc	2
7	1	3	605	mc
8	1	3	605	mc	asd
9	1	3	605	mc	asd		1	3	605	mc	8
10	2	4	605	mc	asddd		2	4	605	mc	2
11	2	4	605	mc			2	4	605	mp	4
12	2	4	605	mp
13	2	4	605	mp
14	2	4	605	mp
15	2	4	605	mp
Sheet1

Cell Formulas
Range	Formula
L5	L5	=SUM(IF(FREQUENCY(IF(LEN($E:$E)>0,MATCH($E:$E,$E:$E,0),""),IF(LEN($E:$E)>0,MATCH($E:$E,$E:$E,0),""))>0,1))
G9:J11	G9	=UNIQUE(A2:D15)
K9:K11	K9	=COUNTIFS($A$2:$A$15,G9,$B$2:$B$15,H9,$C$2:$C$15,I9,$D$2:$D$15,J9)
Dynamic array formulas.

 

[Jescanilla]

in the first sum you have to count 1 asdasd, 1 123, 1 456mc67, 1 blank and 1 asd with the following criteria year 1 week 3 truck 605 and type mc.
that its a total count of 5.
And in the other sum you have 1 asddd and 1 blank with the criteria year 2 week 4 truck 605 and type mc so the count would be 2 as i show in the table

 

[etaf]

how about
=COUNTA((UNIQUE(FILTER($E$2:$E$15,(($A$2:$A$15=G9))*($B$2:$B$15=H9)*($C$2:$C$15=I9)*($D$2:$D$15=J9)))))

Book4
	A	B	C	D	E	F	G	H	I	J	K	L
1	year	week	truck	type	ID
2	1	3	605	mc	asdasd
3	1	3	605	mc	123
4	1	3	605	mc	456mc67		year	week	truck	type	sum
5	1	3	605	mc	456mc67		1	3	605	mc	5	6
6	1	3	605	mc	456mc67		2	4	605	mc	2
7	1	3	605	mc
8	1	3	605	mc	asd
9	1	3	605	mc	asd		1	3	605	mc	5
10	2	4	605	mc	asddd		2	4	605	mc	2
11	2	4	605	mc			2	4	605	mp	1
12	2	4	605	mp
13	2	4	605	mp
14	2	4	605	mp
15	2	4	605	mp
Sheet1

Cell Formulas
Range	Formula
L5	L5	=SUM(IF(FREQUENCY(IF(LEN($E:$E)>0,MATCH($E:$E,$E:$E,0),""),IF(LEN($E:$E)>0,MATCH($E:$E,$E:$E,0),""))>0,1))
G9:J11	G9	=UNIQUE(A2:D15)
K9:K11	K9	=COUNTA((UNIQUE(FILTER($E$2:$E$15,(($A$2:$A$15=G9))*($B$2:$B$15=H9)*($C$2:$C$15=I9)*($D$2:$D$15=J9)))))
Dynamic array formulas.

 

[Jescanilla]

i tried in to a large range and didnt count any, then i add more data to the table and 1 lane of conditional and it must count 5 but it didnt.

Libro KPI mantenimiento (version 3).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
4
5	year	week	truck	type	ID
6	1	3	605	mc	asdasd
7	1	3	605	mc	123
8	1	3	605	mc	456mc67		year	week	truck	type	sum
9	1	3	605	mc	456mc67		1	3	605	mc	6
10	1	3	605	mc	456mc67		2	4	605	mc	2
11	1	3	605	mc			3	5	605	mc	1	must count 5
12	1	3	605	mc	asd
13	1	3	605	mc	asd
14	2	4	605	mc	asddd
15	2	4	605	mc
16	2	4	605	mp
17	2	4	605	mp
18	2	4	605	mp
19	2	4	605	mp
20	1	3	605	mc	456mc672
21	3	5	605	mc
22	3	5	605	mc
23	3	5	605	mc
24	3	5	605	mc
25	3	5	605	mc
26
27
28
29
Sheet4

Cell Formulas
Range	Formula
K9	K9	=COUNTA((UNIQUE(FILTER(E6:E25,(($A$6:$A$25=G9))*($B$6:$B$25=H9)*($C$6:$C$25=I9)*($D$6:$D$25=J9)))))
K10	K10	=COUNTA((UNIQUE(FILTER(E6:E25,(($A$6:$A$25=G10))*($B$6:$B$25=H10)*($C$6:$C$25=I10)*($D$6:$D$25=J10)))))
K11	K11	=COUNTA((UNIQUE(FILTER(E6:E25,(($A$6:$A$25=G11))*($B$6:$B$25=H11)*($C$6:$C$25=I11)*($D$6:$D$25=J11)))))

 

[Jescanilla]

i tried in to a large range and didnt count any, then i add more data to the table and 1 lane of conditional and it must count 5 but it didnt.

Libro KPI mantenimiento (version 3).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
4
5	year	week	truck	type	ID
6	1	3	605	mc	asdasd
7	1	3	605	mc	123
8	1	3	605	mc	456mc67		year	week	truck	type	sum
9	1	3	605	mc	456mc67		1	3	605	mc	6
10	1	3	605	mc	456mc67		2	4	605	mc	2
11	1	3	605	mc			3	5	605	mc	1	must count 5
12	1	3	605	mc	asd
13	1	3	605	mc	asd
14	2	4	605	mc	asddd
15	2	4	605	mc
16	2	4	605	mp
17	2	4	605	mp
18	2	4	605	mp
19	2	4	605	mp
20	1	3	605	mc	456mc672
21	3	5	605	mc
22	3	5	605	mc
23	3	5	605	mc
24	3	5	605	mc
25	3	5	605	mc
26
27
28
29
Sheet4

Cell Formulas
Range	Formula
K9	K9	=COUNTA((UNIQUE(FILTER(E6:E25,(($A$6:$A$25=G9))*($B$6:$B$25=H9)*($C$6:$C$25=I9)*($D$6:$D$25=J9)))))
K10	K10	=COUNTA((UNIQUE(FILTER(E6:E25,(($A$6:$A$25=G10))*($B$6:$B$25=H10)*($C$6:$C$25=I10)*($D$6:$D$25=J10)))))
K11	K11	=COUNTA((UNIQUE(FILTER(E6:E25,(($A$6:$A$25=G11))*($B$6:$B$25=H11)*($C$6:$C$25=I11)*($D$6:$D$25=J11)))))

its has 12000 rows btw

 

[Peter_SSs]

In post 5, why must cell K11 count 5?
All the rows with year = 3 are identical so how do you turn that into 5 uniques?

 

[etaf]

i have misunderstood your post 3 , i thought for the columns A,B,C,D - all the same , then the unique counts was for the column E including a blank
so in your example

POST 5
there 5 identical entries in column A = year3 ,B = week5, C = 605 and D = mc
Now we count the number of unique entries in column E also counting blanks
but column E only has blank entries - and so counting the unique blanks - so that should count as 1 , a it does

based on post 3

in the first sum you have to count 1 asdasd, 1 123, 1 456mc67, 1 blank and 1 asd with the following criteria year 1 week 3 truck 605 and type mc.
that its a total count of 5.

it wasn't until i looked to try and understand why you had 6 - i noticed you had the same year, week etc in row 20 with a different entry (unique) in E - hence 6 instead of 5

So can you explain whats different for the year 3 entries , row 21 to 25 - that instead of 1 you want a 5

Peter_SSs most likely has a better way to do thos over 12,000+ rows

 

[Jescanilla]

Sorry for the misunderstand.

im trying to group certain situations based on year, week truck and when type is mc.
when situation are solve in a shift or even a day i just put the ID in blank, but when it passes more then a day or even a week i need to group it assigning a value in ID that why i must count uniques and the amount of empty cells.

If there is no solution i was thinking start assigning values to empty cells so your formula will do the work perfectly

Thanks by advance

 

[etaf]

sorry still not sure i get it
year 3 has blank ID's and all the columns the same so a count of unique is 1 - but you want 5 - so not counting as unique just blanks
BUT then
year 2 you also have 5 blanks - with all the same in otehr columns - but there you only count the blanks entry as 1 and not 5

sorry confused