Hi,

I have been reading Excel Help and trying to learn how to use IF, SUM, FREQUENCY, MATCH, and LEN functions to count the number of unique values.

I also read a thread (from years ago) which explained FREQUENCY( ) quite well. (Yes, *years ago* but it is still good info.)

I have a question from using "evaluate formula."

Following is from Excel Help. Hope the alignment comes out okay.

******************************************
1
2
3
4
5
6
7
8
9
10
A
Data
986
Dodsworth
67

Buchanan
689
Dodsworth
56
67


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

Count the number of unique text and number values in cells A2:A10 , but do not count blank cells .....

*****************************************
The result as expected is 6.

I did "evaluate formula" but I didn't understand how one step (A below) jumped to another (B below). Please explain.

Step A

=SUM(IF(FREQUENCY({1;2;3;"";5;6;2;8;3},{1;2;3;"";5 ;6;2;8;3})0,1))

Step B

=SUM(IF({1;2;2;1;1;0;1;0;0}0,1))

I don't understand how the two arguments for frequency merged into the one set of array constants. I am missing something here.

Wait, after typing all this, I may be on to something. (Typing it out actually helps me think.) Let me try not to focus on how step A get converted to step B as if there is some kind of calculation (like SUMPRODUCT ( )for example). Let me apply the data set A2:A10 to step B as explained in my reading material.

986 (1), Dodsworth (2), 67 (2), the blank is ignored, Buchanan (1), 689 (1), Dodsworth (0), 56 (1), 67 (0).

That's it. I tried this earlier (i.e. before I started typing), but I was thrown off by the blank and I didn't complete the application. Now that I have decided to skip the blank, it just flows.

1,2,2,1,1,0,1,0

If you compare the above to step B, you will note that I am missing one zero which is the last one. Based on what I read from Help, the range that contains the *formula* should be one more cell than the bin array. In this case, the value for the last (extra) cell is 0.

Please let me know if this assumption is correct and if my overall analysis is all right as well.

Appreciate guidance.

Epinn

The alignment is off. Please ignore 1,2,3, etc.

A1 is Data, A2 is 986 etc. etc.

Epinn

"Epinn" wrote in message ...
Hi,

I have been reading Excel Help and trying to learn how to use IF, SUM, FREQUENCY, MATCH, and LEN functions to count the number of unique values.

I also read a thread (from years ago) which explained FREQUENCY( ) quite well. (Yes, *years ago* but it is still good info.)

I have a question from using "evaluate formula."

Following is from Excel Help. Hope the alignment comes out okay.

******************************************
1
2
3
4
5
6
7
8
9
10
A
Data
986
Dodsworth
67

Buchanan
689
Dodsworth
56
67


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

Count the number of unique text and number values in cells A2:A10 , but do not count blank cells .....

*****************************************
The result as expected is 6.

I did "evaluate formula" but I didn't understand how one step (A below) jumped to another (B below). Please explain.

Step A

=SUM(IF(FREQUENCY({1;2;3;"";5;6;2;8;3},{1;2;3;"";5 ;6;2;8;3})0,1))

Step B

=SUM(IF({1;2;2;1;1;0;1;0;0}0,1))

I don't understand how the two arguments for frequency merged into the one set of array constants. I am missing something here.

Wait, after typing all this, I may be on to something. (Typing it out actually helps me think.) Let me try not to focus on how step A get converted to step B as if there is some kind of calculation (like SUMPRODUCT ( )for example). Let me apply the data set A2:A10 to step B as explained in my reading material.

986 (1), Dodsworth (2), 67 (2), the blank is ignored, Buchanan (1), 689 (1), Dodsworth (0), 56 (1), 67 (0).

That's it. I tried this earlier (i.e. before I started typing), but I was thrown off by the blank and I didn't complete the application. Now that I have decided to skip the blank, it just flows.

1,2,2,1,1,0,1,0

If you compare the above to step B, you will note that I am missing one zero which is the last one. Based on what I read from Help, the range that contains the *formula* should be one more cell than the bin array. In this case, the value for the last (extra) cell is 0.

Please let me know if this assumption is correct and if my overall analysis is all right as well.

Appreciate guidance.

Epinn

Never mind. I have sorted it out all by myself. Evaluate formula is fantastic!

** clear as crystal **

Epinn

"Epinn" wrote in message ...
Hi,

I have been reading Excel Help and trying to learn how to use IF, SUM, FREQUENCY, MATCH, and LEN functions to count the number of unique values.

I also read a thread (from years ago) which explained FREQUENCY( ) quite well. (Yes, *years ago* but it is still good info.)

I have a question from using "evaluate formula."

Following is from Excel Help. Hope the alignment comes out okay.

******************************************
1
2
3
4
5
6
7
8
9
10
A
Data
986
Dodsworth
67

Buchanan
689
Dodsworth
56
67


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

Count the number of unique text and number values in cells A2:A10 , but do not count blank cells .....

*****************************************
The result as expected is 6.

I did "evaluate formula" but I didn't understand how one step (A below) jumped to another (B below). Please explain.

Step A

=SUM(IF(FREQUENCY({1;2;3;"";5;6;2;8;3},{1;2;3;"";5 ;6;2;8;3})0,1))

Step B

=SUM(IF({1;2;2;1;1;0;1;0;0}0,1))

I don't understand how the two arguments for frequency merged into the one set of array constants. I am missing something here.

Wait, after typing all this, I may be on to something. (Typing it out actually helps me think.) Let me try not to focus on how step A get converted to step B as if there is some kind of calculation (like SUMPRODUCT ( )for example). Let me apply the data set A2:A10 to step B as explained in my reading material.

986 (1), Dodsworth (2), 67 (2), the blank is ignored, Buchanan (1), 689 (1), Dodsworth (0), 56 (1), 67 (0).

That's it. I tried this earlier (i.e. before I started typing), but I was thrown off by the blank and I didn't complete the application. Now that I have decided to skip the blank, it just flows.

1,2,2,1,1,0,1,0

If you compare the above to step B, you will note that I am missing one zero which is the last one. Based on what I read from Help, the range that contains the *formula* should be one more cell than the bin array. In this case, the value for the last (extra) cell is 0.

Please let me know if this assumption is correct and if my overall analysis is all right as well.

Appreciate guidance.

Epinn