• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate the number of distinct values

arun2605

New Member
Hi- I have a huge data file and was looking to get some help in calculating the number of distinct values in excel. I have three columns A B & C and column C has Names of people and Column A has which projects they have worked on and Column B has their team names.

I would like to have a formula with which i can find out, in how many projects each person has worked on. I have attached a sample data which is very minimal (the actual data has around 10000 line items). I would be grateful for any suggestions.

Thanks!
Arun
 

Attachments

  • Data Sheet.xlsx
    8.6 KB · Views: 5
Arun

Firstly, Welcome to the Chandoo.org Forums

have you thought about using a simple Pivot Table and using Count instead of Sum?
 
Hello Hui- I have tried a pivot and got the desired information, but there are multile files like this, so if i am able to do this with a formula..it would make my work more easier!! :):):):)
 
HI arun2605,

I can give it a shot. Admittedly, FREQUENCY and MMULT are two functions that I don't visualize very well.
=SUM(IF(FREQUENCY(IF($C$2:$C$1000<>"",IF($C$2:$C$1000=G2,MATCH("~"&$A$2:$A$1000&"",$A$2:$A$1000&"",))),ROW($C$2:$C$1000)-ROW($A$2)+1),1))

Part is red is where we list criteria. If needed, we could do multiple criteria by multiply together, like we do with sumproduct
e.g.,
IF((C2:C10=G2)*(D2:D10=H2),do something,don't do something)

The part in blue, ROW(A2), works as a counter. It just needs to be the first cell in your column. So, if table was in A10:C1000, this part would be $A$10.

Green arrays should be the criteria range.

Pink arrays should be the range you want unique values from.

I'm afraid that's about the limit of my knowledge. Vijay is much better at using the FREQUENCY function, hence why I bookmarked his post in the FAQ. I may not understand, but at least I know how to use it. :p
 
How about this:
upload_2015-2-27_22-59-8.png

Entirely done with 3 formulas
Column Header:
G1: =IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($F$1:F1, $A$2:$A$20), 0)),"") Ctrl+Shift+Enter copy across

Row Labels:
F2: =IFERROR(INDEX($C$2:$C$20, MATCH(0, COUNTIF($F$1:F1, $C$2:$C$20), 0)),"") Ctrl+Shift+Enter copy down

Body:
G2: =COUNTIFS($A$2:$A$32,G$1,$C$2:$C$32,$F2)
copy across and down

see attached
 

Attachments

  • Data Sheet.xlsx
    10.5 KB · Views: 1
Last edited:
Hello Hui- I have tried a pivot and got the desired information, but there are multile files like this, so if i am able to do this with a formula..it would make my work more easier!! :):):):)

As Hui suggested and you your self tried I also think Pivot is the best way, but I could not understand how formula will be easier for multiple files, creating a pivot is just a matter of seconds jobs even to update also it takes much less time than any other array formulas. So you can create pivot for individual file or you can merge data in a master file and than create the pivot. Converting your data to Excel data will take care of future data which will be added to it.

Regards,
 
Back
Top