Counting Unique Entries

D

DCPAus

Board Regular
Joined
Nov 7, 2002
Messages
90
I have a list of products purchased by various customers.

Certain Products are defined as MTO - Made to Order.

I need to count all unique entries for all products where they are MTO ie

Column A Column B
Cars MTO
Bikes Stock
Cars MTO
Shoes MTO
Horses Stock
Cats MTO
Dogs MTO
Cats MTO

Therefore unique MTO products are
3

Is there a simple formula for this ?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Hi

Assuming that the data is in columns A1:B8, enter the formulas

=A1&B1 in C1 and
=IF(AND(COUNTIF($C$1:C1,C1)=1,B1="MTO"),1,0) in D1 and copy down accordingly.

Summing column D will give the total.

Shouldn't it be 4 not 3? Cars, Shoes, Cats and Dogs


Tony
 
Upvote 0
Book26
ABCDE
1ItemTypeTypeDistinctItems
2CarsMTOMTO4
3BikesStock
4CarsMTO
5ShoesMTO
6HorsesStock
7CatsMTO
8DogsMTO
9CatsMTO
Sheet1


E2:

=COUNT(1/FREQUENCY(IF((B2:B9=D2)*(A2:A9<>""),MATCH(A2:A9,A2:A9,0)),ROW(INDIRECT("1:"&COUNTA(A2:A9)))))

which must be confirmed with control+shift+enter instead of just with enter.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Count ifs formula
Replies
2
Views
364
Herakles
H
O
  • Solved
Counting instances of unique entries, with conditions
Replies
1
Views
403
ollyhughes1982
O
A
  • Question
Xlookup, Vlookup and/or VBA for finding, matching and inputting data to separate sheet
Replies
0
Views
545
AlfredDB
A
M
  • Solved
Shopping Cart - Most commonly purchased with?
Replies
9
Views
338
Peter_SSs
Peter_SSs
A
  • Question
Obtaining a Unique List of Values for Data Validation
Replies
1
Views
973
a68tbird
A

Forum statistics

Threads
1,215,537
Messages
6,125,394
Members
449,222
Latest member
taner zz

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