Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
Hi, I am new to excel and wonder if anyone can help me with count.
I have a table e.g aaa aaa bbb bbb ccc ccc I want to count the numbers of text or numbers in the column without the repeated text or numbers. i.e the result should be 3 not 6. I need the count to be able for number as well. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
Try this:
=SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&"")) Biff "MK" wrote in message ... Hi, I am new to excel and wonder if anyone can help me with count. I have a table e.g aaa aaa bbb bbb ccc ccc I want to count the numbers of text or numbers in the column without the repeated text or numbers. i.e the result should be 3 not 6. I need the count to be able for number as well. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
Biff, I see that this formula works, but I don't understand how or why. can
you explain what the formula is doing, for example what does (A1:A6&"") do amd the (A1:A6<""). I've not seen that before and the "Help with this function" doesn't explain it. "Biff" wrote: Try this: =SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&"")) Biff "MK" wrote in message ... Hi, I am new to excel and wonder if anyone can help me with count. I have a table e.g aaa aaa bbb bbb ccc ccc I want to count the numbers of text or numbers in the column without the repeated text or numbers. i.e the result should be 3 not 6. I need the count to be able for number as well. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
The expression: A1:A6&"", concatenates an empty string to the value in each
cell. If a cell is empty then this cell will now only contain the empty string. This is done so that empty cells will not cause a #DIV/0! error. The expression: A1:A6<"", tests each cell in the range to see if it is blank or empty so that it won't be counted. Try this little experiment. Use the sample data posted by the OP. Enter those values in A1:A6. Let's breakdown the formula into its individual components. Enter this formula in C1 and copy down to C6: =A1<"" Enter this formula in D1 and copy down to D6: =COUNTIF(A$1:A$6,A1) Enter this formula in E1 and copy down to E6: =C1/D1 And finally, enter this formula in F1: =SUM(E1:E6) You get the correct result of 3. Now, clear any one of the entries in A1:A6 so that there is an empty cell and see what happens. Now, change the formula in column D to: =COUNTIF(A$1:A$6,A1&"") Copy down to D6. See how that affects the result? Biff "Loris" wrote in message ... Biff, I see that this formula works, but I don't understand how or why. can you explain what the formula is doing, for example what does (A1:A6&"") do amd the (A1:A6<""). I've not seen that before and the "Help with this function" doesn't explain it. "Biff" wrote: Try this: =SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&"")) Biff "MK" wrote in message ... Hi, I am new to excel and wonder if anyone can help me with count. I have a table e.g aaa aaa bbb bbb ccc ccc I want to count the numbers of text or numbers in the column without the repeated text or numbers. i.e the result should be 3 not 6. I need the count to be able for number as well. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
So as long as there are no blanks in A1:A6,
=SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6)) would yield the same result? I think I've got it. Great explantion and example. Thank you. "Biff" wrote: The expression: A1:A6&"", concatenates an empty string to the value in each cell. If a cell is empty then this cell will now only contain the empty string. This is done so that empty cells will not cause a #DIV/0! error. The expression: A1:A6<"", tests each cell in the range to see if it is blank or empty so that it won't be counted. Try this little experiment. Use the sample data posted by the OP. Enter those values in A1:A6. Let's breakdown the formula into its individual components. Enter this formula in C1 and copy down to C6: =A1<"" Enter this formula in D1 and copy down to D6: =COUNTIF(A$1:A$6,A1) Enter this formula in E1 and copy down to E6: =C1/D1 And finally, enter this formula in F1: =SUM(E1:E6) You get the correct result of 3. Now, clear any one of the entries in A1:A6 so that there is an empty cell and see what happens. Now, change the formula in column D to: =COUNTIF(A$1:A$6,A1&"") Copy down to D6. See how that affects the result? Biff "Loris" wrote in message ... Biff, I see that this formula works, but I don't understand how or why. can you explain what the formula is doing, for example what does (A1:A6&"") do amd the (A1:A6<""). I've not seen that before and the "Help with this function" doesn't explain it. "Biff" wrote: Try this: =SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&"")) Biff "MK" wrote in message ... Hi, I am new to excel and wonder if anyone can help me with count. I have a table e.g aaa aaa bbb bbb ccc ccc I want to count the numbers of text or numbers in the column without the repeated text or numbers. i.e the result should be 3 not 6. I need the count to be able for number as well. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
So as long as there are no blanks in A1:A6,
=SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6)) would yield the same result? Yes. You're welcome. Thanks for the feedback! Biff "Loris" wrote in message ... So as long as there are no blanks in A1:A6, =SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6)) would yield the same result? I think I've got it. Great explantion and example. Thank you. "Biff" wrote: The expression: A1:A6&"", concatenates an empty string to the value in each cell. If a cell is empty then this cell will now only contain the empty string. This is done so that empty cells will not cause a #DIV/0! error. The expression: A1:A6<"", tests each cell in the range to see if it is blank or empty so that it won't be counted. Try this little experiment. Use the sample data posted by the OP. Enter those values in A1:A6. Let's breakdown the formula into its individual components. Enter this formula in C1 and copy down to C6: =A1<"" Enter this formula in D1 and copy down to D6: =COUNTIF(A$1:A$6,A1) Enter this formula in E1 and copy down to E6: =C1/D1 And finally, enter this formula in F1: =SUM(E1:E6) You get the correct result of 3. Now, clear any one of the entries in A1:A6 so that there is an empty cell and see what happens. Now, change the formula in column D to: =COUNTIF(A$1:A$6,A1&"") Copy down to D6. See how that affects the result? Biff "Loris" wrote in message ... Biff, I see that this formula works, but I don't understand how or why. can you explain what the formula is doing, for example what does (A1:A6&"") do amd the (A1:A6<""). I've not seen that before and the "Help with this function" doesn't explain it. "Biff" wrote: Try this: =SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&"")) Biff "MK" wrote in message ... Hi, I am new to excel and wonder if anyone can help me with count. I have a table e.g aaa aaa bbb bbb ccc ccc I want to count the numbers of text or numbers in the column without the repeated text or numbers. i.e the result should be 3 not 6. I need the count to be able for number as well. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula to count spinoffs | Excel Worksheet Functions | |||
Subtotals by count | Excel Discussion (Misc queries) | |||
Count consecutive dates only | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions |