Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
Subtotal counting formula in cells | Excel Discussion (Misc queries) | |||
Counting Cells | Excel Discussion (Misc queries) | |||
Counting filled cells in excel | Excel Worksheet Functions | |||
Counting Occurrence of Text within Text in Cells in Range. | Excel Worksheet Functions |