Troubleshoot:Counting Unique Values in Different Categories and stuff..... Error

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.....

Download SmartPCFixer to Fix It (Free)

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.  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


Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: [Solved] Convert text format,Solution to Problem: Copy \/ Combine all Sheets into One New \/ Separate Sheet,Fast Solution to Problem: Control Panel will not open,Troubleshoot:Control Panel/Task Manager - Programs not appearing Error,Troubleshooting:Could not find EbookLauncher.xml file!,Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly
Comments