Count if unique value + empty cell with four criteria

J

Jescanilla

New Member
Joined
Aug 17, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKL
5yearweektrucktypeID
613605mcasdasd
713605mc123
813605mc456mc67yearweektrucktypesum
913605mc456mc6713605mc56
1013605mc456mc6724605mc2
1113605mc
1213605mcasd
1313605mcasd
1424605mcasddd
1524605mc
1624605mp
1724605mp
1824605mp
1924605mp
Sheet4
Cell Formulas
RangeFormula
L9L9=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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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
ABCDEFGHIJKL
1yearweektrucktypeID
213605mcasdasd
313605mc123
413605mc456mc67yearweektrucktypesum
513605mc456mc6713605mc56
613605mc456mc6724605mc2
713605mc
813605mcasd
913605mcasd13605mc8
1024605mcasddd24605mc2
1124605mc24605mp4
1224605mp
1324605mp
1424605mp
1524605mp
Sheet1
Cell Formulas
RangeFormula
L5L5=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:J11G9=UNIQUE(A2:D15)
K9:K11K9=COUNTIFS($A$2:$A$15,G9,$B$2:$B$15,H9,$C$2:$C$15,I9,$D$2:$D$15,J9)
Dynamic array formulas.
 
Upvote 0
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
 
Upvote 0
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
ABCDEFGHIJKL
1yearweektrucktypeID
213605mcasdasd
313605mc123
413605mc456mc67yearweektrucktypesum
513605mc456mc6713605mc56
613605mc456mc6724605mc2
713605mc
813605mcasd
913605mcasd13605mc5
1024605mcasddd24605mc2
1124605mc24605mp1
1224605mp
1324605mp
1424605mp
1524605mp
Sheet1
Cell Formulas
RangeFormula
L5L5=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:J11G9=UNIQUE(A2:D15)
K9:K11K9=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.
 
Upvote 0
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
ABCDEFGHIJKLM
4
5yearweektrucktypeID
613605mcasdasd
713605mc123
813605mc456mc67yearweektrucktypesum
913605mc456mc6713605mc6
1013605mc456mc6724605mc2
1113605mc35605mc1must count 5
1213605mcasd
1313605mcasd
1424605mcasddd
1524605mc
1624605mp
1724605mp
1824605mp
1924605mp
2013605mc456mc672
2135605mc
2235605mc
2335605mc
2435605mc
2535605mc
26
27
28
29
Sheet4
Cell Formulas
RangeFormula
K9K9=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)))))
K10K10=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)))))
K11K11=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)))))
 
Upvote 0
Jescanilla said:
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
ABCDEFGHIJKLM
4
5yearweektrucktypeID
613605mcasdasd
713605mc123
813605mc456mc67yearweektrucktypesum
913605mc456mc6713605mc6
1013605mc456mc6724605mc2
1113605mc35605mc1must count 5
1213605mcasd
1313605mcasd
1424605mcasddd
1524605mc
1624605mp
1724605mp
1824605mp
1924605mp
2013605mc456mc672
2135605mc
2235605mc
2335605mc
2435605mc
2535605mc
26
27
28
29
Sheet4
Cell Formulas
RangeFormula
K9K9=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)))))
K10K10=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)))))
K11K11=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)))))
Click to expand...

its has 12000 rows btw
 
Upvote 0
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?
 
Upvote 0
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.
Click to expand...

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
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Summing Values
Replies
3
Views
543
LazyBug
LazyBug
F
  • Solved
How to show the nth occurance based on another column
Replies
6
Views
310
feni1388
F
M
  • Question Question
Count unique cells based on column of blank cells
2
Replies
13
Views
534
Peter_SSs
Peter_SSs
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
338
Asbestos_Jen
Asbestos_Jen
gtd526
  • Question Question
Create List of Cell Values with Criteria
Replies
1
Views
397
gtd526
gtd526

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top