Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Counting Cells, excluding those which have appeared before.

Hi there,

I'm wondering how I'd be able to have a formula to calculate the number
of cells in a row which have appeared once and even though the value
appears many times in that row, I'd only count it as once. For
instance:

A
----
M
M
N
E
F
G
T
S
T
S

The result I'd want is 7, not taking into consideration the repeated
alphabets M, T and S. I wish to only count each alphabet once, not
matter whether if they are repeated or not.

Thanks!

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default Counting Cells, excluding those which have appeared before.

http://www.cpearson.com/excel/duplic...CountingUnique

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"BigMac" wrote in message oups.com...
| Hi there,
|
| I'm wondering how I'd be able to have a formula to calculate the number
| of cells in a row which have appeared once and even though the value
| appears many times in that row, I'd only count it as once. For
| instance:
|
| A
| ----
| M
| M
| N
| E
| F
| G
| T
| S
| T
| S
|
| The result I'd want is 7, not taking into consideration the repeated
| alphabets M, T and S. I wish to only count each alphabet once, not
| matter whether if they are repeated or not.
|
| Thanks!
|


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Counting Cells, excluding those which have appeared before.

My god this is a godsend. I really don't know what I've done without
this. Thank you so much! It's guys like you that make EXCEL so much
easier to use. Thanks again!

Niek Otten wrote:
http://www.cpearson.com/excel/duplic...CountingUnique

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"BigMac" wrote in message oups.com...
| Hi there,
|
| I'm wondering how I'd be able to have a formula to calculate the number
| of cells in a row which have appeared once and even though the value
| appears many times in that row, I'd only count it as once. For
| instance:
|
| A
| ----
| M
| M
| N
| E
| F
| G
| T
| S
| T
| S
|
| The result I'd want is 7, not taking into consideration the repeated
| alphabets M, T and S. I wish to only count each alphabet once, not
| matter whether if they are repeated or not.
|
| Thanks!
|


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 477
Default Counting Cells, excluding those which have appeared before.

In say cell C1 type in:
=SUM(1/COUNTIF(A1:A10,A1:A10)) <<Do Not Press Enter As Usual; Instead
Press (the 3 keys simultaneously) Ctrl + Shift + Enter -- This should
"Add" internally two { } characters - ONe at the beginng and one at the end
These {} can not be entered at the keyboard.

HTH



"BigMac" wrote:

Hi there,

I'm wondering how I'd be able to have a formula to calculate the number
of cells in a row which have appeared once and even though the value
appears many times in that row, I'd only count it as once. For
instance:

A
----
M
M
N
E
F
G
T
S
T
S

The result I'd want is 7, not taking into consideration the repeated
alphabets M, T and S. I wish to only count each alphabet once, not
matter whether if they are repeated or not.

Thanks!


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
Linking Groups of cells between workbooks vnacj-joe Excel Discussion (Misc queries) 4 June 14th 07 05:18 PM
Subtotal counting formula in cells jimar Excel Discussion (Misc queries) 1 June 1st 06 01:53 AM
Counting Cells LostNFound Excel Discussion (Misc queries) 1 March 27th 06 07:19 PM
Counting filled cells in excel Alex Wilson Excel Worksheet Functions 5 September 19th 05 11:01 PM
Counting Occurrence of Text within Text in Cells in Range. Jeremy N. Excel Worksheet Functions 1 September 8th 05 05:16 AM


All times are GMT +1. The time now is 05:14 PM.

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"