Counting unique entries

[Peter99]

My question is related to Excel 97 and is basically twofold.

1) I want to count the number of unique occurences in a list. In the example below the count of both Column A & B would be 3.

Column A
Column B

Item type
Department

A
Accounts

A
Sales

C
Sales

B
Sales

B
Accounts

B
Admin

C
Admin

A
Sales



2) Using the same example data, I want to count the number of departments that use the individual Item types.

Required result being; Type A = 2 (Accounts & Sales)
Type B = 3 (Accounts, Sales & Admin)
Type C = 2 (Admin & Sales)

I have given column B an Alpha entry as this is how I would like it idealy to be, but the departments could be referenced numerically if this would assist.

 

[Jack in the UK]

Test post!

 

[Jack in the UK]

I would advance filter and set the ranges..

Now tick unique .. thats done ok and the worlds your oyster. This wil automatically show count in blue ie 10 of 15 records theese the count you requier (hope that right memory there) suggest to 3 filters 1 and release the B /// then c//

1) I want to count the number of unique occurences in a list. In the example below the count of both Column A & B would be 3.

Column A
Column B

Item type
Department

A
Accounts

A
Sales

C
Sales

B
Sales

B
Accounts

B
Admin

C
Admin

A
Sales

Agaim i suggest filter or advanced filter.

HTH


2) Using the same example data, I want to count the number of departments that use the individual Item types.

Required result being; Type A = 2 (Accounts & Sales)
Type B = 3 (Accounts, Sales & Admin)
Type C = 2 (Admin & Sales)

I have given column B an Alpha entry as this is how I would like it idealy to be, but the departments could be referenced numerically if this would assist.

[/quote]

 

[Aladin Akyurek]

I'll assume that A1:B9 houses the sample data you provided:

{"item type","department";
"A","Accounts";
"A","Sales";
"C","Sales";
"B","Sales";
"B","Accounts";
"B","Admin";
"C","Admin";
"A","Sales"}

In C2 enter and copy down:

=A2&"-@-"&B2

Hide C if you so wish.

In D2 enter: # Uniq Items
In E2 array-enter:

=SUM(IF(LEN(A2:A9),1/COUNTIF(A2:A9,A2:A9)))

In D3 enter: # Uniq Deps
In E3 array-enter:

=SUM(IF(LEN(B2:B9),1/COUNTIF(B2:B9,B2:B9)))

In D4 enter: A
In D5 enter: B
In D6 enter: C

Note that D4:D6 houses a unique list of item types, which can be created with Advanced Filter.

In E4 array-enter and copy down:

=SUM(IF($A$2:$A$9=D4,1/COUNTIF($C$2:$C$9,$C$2:$C$9)))

In order array-enter a formula, you need to hit control+shift+enter, instead of just enter.

Aladin