What are a few unique Chinese values?

Counting unique values

• QUESTION: I have a question regarding how to count unique numbers in a list within Excel 2003. For example if i have a list such as: No's Filter 1 0 2 0 2 1 3 1 5 0 5 1 1 0 2 1 3 1 If I want to count all of the unique numbers in the 'No's' column I use the formula =sum(if(frequency(a2:a10,a2:a10),1)), is this the easiest/correct way to do this? I then get stuck at the next bit, I want to count all of the unqiue values in the 'No's' column where the 'Filter' column has a 1, how would I go about doing this? Any help would be much appreciated. ANSWER: Sonny, assume your data is in A2:A10, then =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1)) gives me the unique count. for a conditional unique count based on the criteria you state then I would adjust the formula like so: =SUM(IF(FREQUENCY(IF(B2:B10=1,A2:A10),A2:A10)>0,1)) this formula is an array formula and must be entered with Ctrl+shift+enter rather than just enter. ---------- FOLLOW-UP ---------- QUESTION: Thanks I just have a couple of questions to help me understand the solution: 1. In the first formula you've added ">0", what does this do? 2. Can you explain how the second IF statement is working within the frequency forumula. I was of the opinion frequency works by the first argument needs to be the values you want to count and the second the groups you want your values to be in? Sonny

• Answer:

  Sonny, the >0 really doesn't do anything more than your original formula. It is just more explicit. the results of frequency is a count in each bin. Since the bins remove any duplicate entries, so your data is counted by matching number. the results of the Frequency is 10 numbers that are either zero (no matches or matching one of the numbers. so you would get an array like {2;3;2;0;2;0;0;0;0;0} when that array is passed to the IF statement, numbers greater than zero are considered True and zero numbers are considered false. {2;3;2;0;2;0;0;0;0;0}>0 just makes this more explicit (easier to read) In the second formula the construct IF(B2:B10=1,A2:A10), produces and array of false, false, 2, 3, false, 5, false, 2, 3 as the first argument to Frequency rather than the original 1,2,2,3,5,5,1,2,3 so it doesn't change the role of the first argument of frequency - it just removes the values that you don't want considered and replaces them with False - but it does it virtually in the formula so you then just get the numbers next to 1's in column B. If you go to the formula bar and select subsets of your formula such as IF(B2:B10=1,A2:A10) and hit F9, it will evaluate that portion of the formula and show you the array it will produce. Then hit the escape key to restore that portion of the formula. You can do this to complete expression that is a subset of the formula (or the entire formula). this will help you see what the formula is doing. (don't forget to hit the escape key to revert back). hope that explains it.