Count unique occurrence against a category Excel 2007

Tenal

New Member
Joined
Sep 10, 2015
Messages
5
Hi all,

In its simplest form, i am trying to create a formula which will automatically count the number of unique references in Column A for a category in column B.

In the basic example below the solution would be that their are 4 unique references labelled under Apple and 2 unique references for bananas.

Your help in this matter is greatly appreciated.

Many thanks

Tenal

Note that the references and category values are text based.
ReferenceCategory
CR1Apple
CR2Apple
CR3Apple
CR1Apple
CR4Apple
CR1Banana
CR1Banana
CR2Banana
CR2Banana

<tbody>
</tbody>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
maybe something like...

E2=SUM(IF(FREQUENCY(IF($A$2:$A$10<>"",IF($B$2:$B$10=D2,MATCH($A$2:$A$10,$A$2:$A$10,0))),ROW($A$2:$A$10)-ROW($A$2)+1),1)) Control Shift Enter

Row\Col
A​
B​
C​
D​
E​
1​
ReferenceCategoryCategoryUnique
2​
CR1AppleApple
4​
3​
CR2AppleBanana
2​
4​
CR3Apple
5​
CR1Apple
6​
CR4Apple
7​
CR1Banana
8​
CR1Banana
9​
CR2Banana
10​
CR2Banana

<tbody>
</tbody>
 
Upvote 0
Hi Weazel,

Can structured references be used in this formula? Also, do I need the category field or can I drop the formula in column C and have it show "4" next to each apple and "2" next to each Banana?

Thanks,
Chris
 
Upvote 0
Hi Weazel,

Can structured references be used in this formula? Also, do I need the category field or can I drop the formula in column C and have it show "4" next to each apple and "2" next to each Banana?

Thanks,
Chris

ReferenceCategoryDistinct #
CR1Apple4
CR2Apple4
CR3Apple4
CR1Apple4
CR4Apple4
CR1Banana2
CR1Banana2
CR2Banana2
CR2Banana2

<tbody>
</tbody>

In C2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(1-([Reference]=""),IF([Category]=[@Category],MATCH([Reference],[Reference],0))),ROW([Reference])-ROW([@Reference])+1),1))
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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