Count unique value

S

SARABECK

Board Regular
Joined
Jan 5, 2012
Messages
132
Hello,

I have products that is loaded to multiple tanks.

For example, product ABC1234 got loaded to tank: Apple and Orange, therefore the unique count of the number tanks is 2.

I found a formula (linked below) that helps count the number of unique tanks for each of the products, but for some reason it only works for some of the products. ABC44205 was loaded to Melon and Apple but the unique count is 1. Can someone please help and please let me know what am i doing wrong?

Code: 
SUM(IF(FREQUENCY(IF($U$1:$U$104=U14,IF($V$1:$V$104<>"",MATCH($V$1:$V$104,$V$1:$V$104,0))),ROW($V$1:$V$104)-ROW(U14)+1),1))

PRODUCTTANKSUNIQUE TANKS
ABC1234ORANGE2
ABC1234ORANGE2
ABC1234ORANGE2
ABC1234ORANGE2
ABC46005KIWI2
XLX770MANGO2
ABC1424GRAPE2
ABC44205MELON1
ABC46005MELON2
ABC36010CHERRY1
XLX770CHERRY2
ABC46014CHERRY1
ABC1424APPLE1
ABC44287APPLE1
ABC46014APPLE1
ABC1234APPLE2
ABC36010APPLE1
ABC44205APPLE1
ABC44205APPLE1
ABC44205APPLE1
ABC44287MELON1
ABC44287MELON1
ABC44287MELON1

<tbody>
</tbody>


Here is my file:
http://s000.tinyupload.com/?file_id=08066986863866212145


http://www.mrexcel.com/forum/excel-...-count-unique-values-matching-criteria-3.html

Thank you.
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Have you tried the unique counter, available in Excel 2013 and later?


or maybe:


Excel 2010
ABCDE
1LOTSTANKS
2ABC1234ORANGE 22
3ABC1234ORANGE 22
4ABC1234ORANGE 22
5ABC1234ORANGE 22
6ABC46005KIWI 22
7XLX770MANGO 22
8ABC1424GRAPE 22
9ABC44205MELON 12
10ABC46005MELON 22
11ABC36010CHERRY 12
12XLX770CHERRY 22
13ABC46014CHERRY 12
14ABC1424APPLE 12
15ABC44287APPLE 12
16ABC46014APPLE 11
17ABC1234APPLE 22
18ABC36010APPLE 11
19ABC44205APPLE 11
20ABC44205APPLE 11
21ABC44205APPLE 11
22ABC44287MELON 11
23ABC44287MELON 11
24ABC44287MELON 11
Sheet1
Cell Formulas
RangeFormula
E2{=SUM(IF(FREQUENCY(IF($A$2:$A$24=A2,IF($B$2:$B$24<>"",MATCH($B$2:$B$24,$B$2:$B$24,0))),ROW($B$2:$B$24)-ROW(A2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Have you tried the unique counter, available in Excel 2013 and later?
Click to expand...


Thank you sheetspread for replying. No I haven't. There is just too much data in the tab that is why i wanted to try the formula.

Your formula is having the same issue as mine, please look at product ABC44205
ROW 9, 19,20,21. It was loaded to tank Melon and Apple. Unique count should be 2.

Thank you.
 
Last edited:
Upvote 0
Right, should they all be 2?

If so, next attempt:


Excel 2010
ABCDE
1LOTSTANKS
2ABC1234ORANGE 22
3ABC1234ORANGE 22
4ABC1234ORANGE 22
5ABC1234ORANGE 22
6ABC46005KIWI 22
7XLX770MANGO 22
8ABC1424GRAPE 22
9ABC44205MELON 12
10ABC46005MELON 22
11ABC36010CHERRY 12
12XLX770CHERRY 22
13ABC46014CHERRY 12
14ABC1424APPLE 12
15ABC44287APPLE 12
16ABC46014APPLE 12
17ABC1234APPLE 22
18ABC36010APPLE12
19ABC44205APPLE 12
20ABC44205APPLE 12
21ABC44205APPLE 12
22ABC44287MELON 12
23ABC44287MELON 12
24ABC44287MELON 12
Sheet1
Cell Formulas
RangeFormula
E2{=SUM(IF(FREQUENCY(IF($A$2:$A$24=A2,IF($B$2:$B$24<>"",MATCH("~"&$B$2:$B$24,$B$2:$B$24&"",0))),ROW(B2:B24)-ROW(A1)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.


The second to last row range has to be relative I think
 
Last edited:
Upvote 0
Or in your file:

=SUM(IF(FREQUENCY(IF($U$1:$U$104=U14,IF($V$1:$V$104<>"",MATCH($V$1:$V$104,$V$1:$V$104,0))),ROW(V1:V104)-ROW(U14)+1),1))
 
Upvote 0
Thank you sheetspread. If you unfilter my file i have about 104 rows of data. Unfortunately, i'm getting the same results. I should have about 8 products being loaded to 2 unique tanks.


EDITED:

I played with your formula a little

Do you think the it should be:

=SUM(IF(FREQUENCY(IF($A$1:$A$104=A2,IF($B$1:$B$104<>"",MATCH("~"&$B$1:$B$104,$B$1:$B$104&"",0))),ROW($B$1:$B$104)-ROW($A$1)+1),1))

ROW($A$1) to be absolute?

It seems to working but not sure if this is the correct way.

Thank you.

{=SUM(IF(FREQUENCY(IF($A$2:$A$24=A2,IF($B$2:$B$24<>"",MATCH("~"&$B$2:$B$24,$B$2:$B$24&"",0))),ROW(B2:B24)-ROW(A1)+1),1))}
 
Last edited by a moderator:
Upvote 0
SARABECK said:
Hello,

I have products that is loaded to multiple tanks.

For example, product ABC1234 got loaded to tank: Apple and Orange, therefore the unique count of the number tanks is 2.

I found a formula (linked below) that helps count the number of unique tanks for each of the products, but for some reason it only works for some of the products. ABC44205 was loaded to Melon and Apple but the unique count is 1. Can someone please help and please let me know what am i doing wrong?

Code: 
SUM(IF(FREQUENCY(IF($U$1:$U$104=U2,IF($V$1:$V$104<>"",MATCH($V$1:$V$104,$V$1:$V$104,0))),ROW($V$2:$V$104)-ROW(U14)+1),1))

PRODUCTTANKSUNIQUE TANKS
ABC1234ORANGE2
ABC1234ORANGE2
ABC1234ORANGE2
ABC1234ORANGE2
ABC46005KIWI2
XLX770MANGO2
ABC1424GRAPE2
ABC44205MELON1
ABC46005MELON2
ABC36010CHERRY1
XLX770CHERRY2
ABC46014CHERRY1
ABC1424APPLE1
ABC44287APPLE1
ABC46014APPLE1
ABC1234APPLE2
ABC36010APPLE1
ABC44205APPLE1
ABC44205APPLE1
ABC44205APPLE1
ABC44287MELON1
ABC44287MELON1
ABC44287MELON1

<tbody>
</tbody>


Here is my file:
http://s000.tinyupload.com/?file_id=08066986863866212145


http://www.mrexcel.com/forum/excel-...-count-unique-values-matching-criteria-3.html

Thank you.
Click to expand...

It should be, control+shift+enter:

=SUM(IF(FREQUENCY(IF($U$2:$U$104=U2,IF($V$2:$V$104<>"",MATCH($V$2:$V$104,$V$2:$V$104,0))),ROW($V$2:$V$104)-ROW($V$2)+1),1))

assuming that U1 and V1 houses headers.

SARABECK said:
Thank you sheetspread. If you unfilter my file i have about 104 rows of data. Unfortunately, i'm getting the same results. I should have about 8 products being loaded to 2 unique tanks.


EDITED:

I played with your formula a little

Do you think the it should be:

=SUM(IF(FREQUENCY(IF($A$1:$A$104=A2,IF($B$1:$B$104<>"",MATCH("~"&$B$1:$B$104,$B$1:$B$104&"",0))),ROW($B$1:$B$104)-ROW($A$1)+1),1))

ROW($A$1) to be absolute?

It seems to working but not sure if this is the correct way.

[...]
Click to expand...

If A1:B1 houses headers, it should be, control+shift+enter and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$104=$A2,IF($B$2:$B$104<>"",MATCH("~"&$B$2:$B$104,$B$2:$B$104&"",0))),ROW($B$2:$B$104)-ROW($B$2)+1),1))
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Copy Values to an Adjacent Column per unique Customer
Replies
3
Views
275
Joe4
Joe4
T
  • Question
Count Unique Values
Replies
1
Views
439
awoohaw
awoohaw
E
  • Solved
Count unique values with 2 conditions
2
Replies
12
Views
625
Fluff
Fluff
K
  • Solved
Flagging Unique and Duplicate values via VBA
Replies
2
Views
595
Kiwihiker
K
T
  • Solved
Count If Distinct Values based on Criteria
Replies
6
Views
796
hrayani
H

Forum statistics

Threads
1,215,352
Messages
6,124,451
Members
449,161
Latest member
NHOJ

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