#1   Report Post  
Posted to microsoft.public.excel.misc
MK MK is offline
external usenet poster
 
Posts: 57
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 39
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 39
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need formula to count spinoffs jamescarvin Excel Worksheet Functions 1 July 14th 06 04:07 PM
Subtotals by count PineRest Excel Discussion (Misc queries) 1 May 10th 06 05:09 PM
Count consecutive dates only [email protected] Excel Discussion (Misc queries) 0 May 4th 06 03:58 PM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 8 May 18th 05 04:23 AM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 0 May 15th 05 08:14 PM


All times are GMT +1. The time now is 10:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"