## Counting Unique Values in Different Categories and stuff.....

Hi, you technical staffs at microsoft and EXCEL pros in this forum:

[Caution: This is a long and tough problem but please be patient to read it through because it reaaaally makes sense to a lot of people.]

My work necessitates me using EXCEL and tackling difficult formula on a daily basis.
And counting unique values in different categories is very important to me.

But there're two problems:
Problem #1:
I’ve tried all
3 formulas offered by EXCEL help file for counting unique values among duplicates.
Only one worked well.
The other two didn't.

Below is a much-simplified example of my problem:
(Background:  16 men in different colors of jackets are having a party, some of them sharing same first names.)

.
A

B

C

1

First Names

Color of jackets

Shape of Face

2

John

yellow

round

3

Alan

purple

round

4

Sam

red

round

5

Tom

blue

oblong

6

John

blue

oblong

7

Tom

yellow

oblong

8

Tom

yellow

oblong

9

Sam

red

oblong

10

Alan

red

oblong

11

Timothy

blue

diamond

12

Sam

purple

diamond

13

Sam

red

diamond

14

Alan

red

diamond

15

Timothy

red

diamond

16

John

blue

triangle

17

Timothy

blue

triangle

OK, now firstly I want to count the number of different first names.
The following is a list of

all 3 formulas offered by Excel help file and the respective results I got:

Formula

Value Returned

=SUM(IF(FREQUENCY(MATCH(A2:A17,A2:A17,0),MATCH(A2:A17,A2:A17,0))>0,1))

5

=SUM(IF(FREQUENCY(IF(LEN(A2:A17)>0,MATCH(A2:A17, A2:A17,0),""), IF(LEN(A2:A17)>0,MATCH(A2:A17, A2:A17,0),""))>0,1))

#VALUE!

=SUM(IF(FREQUENCY(A2:A17,A2:A17)>0,1))

0

Clearly , the latter 2 formulas don’t work.
(the 3rd one works, but the result is wrong.)
Would you technical staffs have a look at it? (please try it out on your computers, many many thanks.)

Problem #2:
Counting unique values among duplicates is not enough for me.
I need to count unique values in certain categories (sometimes in multiple categories, AND or OR).

For instance,  I now need to count the number of different names of round-faced men in
yellow jackets.  What the formula should be like?

I assume it should be something like:
=IF(AND(B2:B17, "yellow",C2:C17,"round"), SUM(IF(FREQUENCY(MATCH(A2:A17,A2:A17,0),MATCH(A2:A17,A2:A17,0))>0,1)),

But I have no idea how to correctly combine the two parts.

The 2nd question is a tough one, I know.
But it makes a lot of sense.  And again, would you please have a look into it?

Using the filter is out of the question because the amount of data and categories I work with is huge and they keep updating.
I can't filter all the time.

I’ve tried copying data conditionally into new columns and then count unique values in new columns (which involves very sophisticated formula and creating numerous new columns). It worked well but still not efficient. (Again, I have
to say the number of the categories of my data is bigger than you think.)

Thanks for reading my message .Hope you can see my problem and hope there's a desirable solution.

Cheers and regards,
Pooh.

## Keys to the Problem Counting Unique Values in Different Categories and stuff.....

Hi,

Problem 1.  Of the 3 formula you posted these two works fine but note they are both ARRAY formula so must be entered with CTRL+Shift+Enter

=SUM(IF(FREQUENCY(MATCH(A2:A17,A2:A17,0),MATCH(A2:A17,A2:A17,0))>0,1))

=SUM(IF(FREQUENCY(IF(LEN(A2:A17)>0,MATCH(A2:A17, A2:A17,0),""), IF(LEN(A2:A17)>0,MATCH(A2:A17, A2:A17,0),""))>0,1))

This too works fine but not on your data, it works for numbers only

=SUM(IF(FREQUENCY(A2:A17,A2:A17)>0,1))

Problem 2.
To count round faced men in yellow jackets you can use this wher D1 = Yellow and E1 = Round

=SUMPRODUCT(((B2:B17=D1)*(C2:C17=E1))/COUNTIFS(A2:A17,A2:A17&"",B2:B17,B2:B17&"",C2:C17,C2:C17&""))

## Check Windows System Requirements

This error can be due to some configuration requirements for you system. If your PC hardware is not up to par, then make sure you upgrade where necessary before continuing:

Microsoft Windows Requirements :

• 1 gigahertz (GHz) or faster 32-bit (x86) or 64-bit (x64) processor
• 16 GB available hard disk space (32-bit) or 20 GB (64-bit)

## Recommended Method to Repair the Problem: Counting Unique Values in Different Categories and stuff.....:

### How to Fix Counting Unique Values in Different Categories and stuff..... with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your system.&nbsp; Open it, and it will perform a scan for your computer. The junk files will be shown in the scan result.

2. After the scan is done, you can see the errors and problems need to be fixed. Click Fix All.

3. When the Fixing part is finished, your computer has been speeded up and the errors have been removed