Counting Unique Values

E

Epinn

Roger,

Ah, that's why you are the expert. (I laughed at my formula after I have seen yours.) I am glad I posted and you responded. Lately, I have been learning about CHAR and I am attracted to it while I have forgotten the simple things in life. <g> I thought I needed REPT to catch all. I read an example of *adding* trailing spaces and REPT is used. Is it true that we need REPT when adding trailing spaces?

Thank you for helping me all the time. By the way, have you read my post about using PivotTable to check for duplicates?

Epinn

Absolutely no difference to the much simpler
=SUBSTITUTE(A1," ","")

--
Regards

Roger Govier


This is how I would remove trailing spaces.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Please correct me if I am wrong. Thanks.

Epinn

The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there
are no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in
the
column. Can anyone help?

thanks,
Bob
 
E

Epinn

RD,

Thank you for asking. Sometimes I thought people could read my mind. <g>

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

Totally agree. I am very much aware of what each formula in this thread does as I have spent hours experimenting. Biff's formula is what I live by when it comes to counting unique values. It just doesn't take care of blanks but much more. As JMB highlighted, unlike the FREQUENCY formula which takes care of blanks and numbers, Biff's formula will take care of *text*, numbers and blanks. Sounds like a one-fits-all formula. I should memorize it by heart. <g>

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. <g>

Epinn

Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*.">>>

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob
 
R

Ragdyer

When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!<bg>
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



RD,

Thank you for asking. Sometimes I thought people could read my mind. <g>

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. <g>

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g>

Epinn

Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*.">>>

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob
 
E

Epinn

Ragdyer is my "handle"......<<

You sound hitech by using the word "handle." When I see the word "handle," I think TINYURL ...... <g>

So, you have a story to tell about your user ID as well. Dyer is a very legitimate surname.

If I don't hear a "no," I'll call you Rick from now on. Actually, RD has less keystrokes. Well, let my mood decide.

Epinn

When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!<bg>
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



RD,

Thank you for asking. Sometimes I thought people could read my mind. <g>

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. <g>

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g>

Epinn

Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*.">>>

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob
 
R

Ragdyer

You very nonchalantly bypassed *my* question though!<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

You sound hitech by using the word "handle." When I see the word "handle,"
I think TINYURL ...... <g>

So, you have a story to tell about your user ID as well. Dyer is a very
legitimate surname.

If I don't hear a "no," I'll call you Rick from now on. Actually, RD has
less keystrokes. Well, let my mood decide.

Epinn

When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!<bg>
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



RD,

Thank you for asking. Sometimes I thought people could read my mind. <g>

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. <g>

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g>

Epinn

Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*.">>>

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob
 
E

Epinn

I think I'll use TRIM( ) if you have no objection.

Epinn

Absolutely no difference to the much simpler
=SUBSTITUTE(A1," ","")

--
Regards

Roger Govier


This is how I would remove trailing spaces.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Please correct me if I am wrong. Thanks.

Epinn

The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there
are no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in
the
column. Can anyone help?

thanks,
Bob
 
B

Bob Phillips

That is because I too was fascinated by your handle, so I looked it up, and
I remember that it was in response to Stephen :).


Bob
 
B

Bob Phillips

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!<bg>
Click to expand...


Whoever suggested that? I remember when 'onedaywhen' was outed as Jamie
Collins, I always thought it was a shame that he dropped the handle, it gave
a certain panache to his posts. I hope you never drop RagDyer, it adds
diversity to the group.
 
J

Jamie Collins

Bob said:
I remember when 'onedaywhen' was outed as Jamie
Collins, I always thought it was a shame that he dropped the handle, it gave
a certain panache to his posts.
Click to expand...

Google doesn't seem to want me to drop the handle either, Bob. I've
long since removed it as a nickname from my profile, however it still
gets applied unless I've subscribed to a group (whatever that means).
So about half my replies are attributed to onedaywhen (try a google
search of recent posts).

FWIW I reverted to using my given name due to negative feedback: one
person thought it was a threat, another suggested it was a pseudonym to
hide behind. Neither was the case. A so-called 'real' name seems to be
preferred.

Jamie.

--
 
B

Bob Phillips

Jamie,

Good to hear from you again. I think I remember you telling me that once
before, odd isn't it?

That negative feedback you refer to was presumably the same sort of rubbish
that Ragdyer was getting. He's more stubborn than you though <G>.

Anyway, I hope that you are well and all is good in ADO/SQL-land.
 
E

Epinn

It is amazing that Jamie could hear Bob mention his/her name and showed up "instantly."

RD, I like handles and you know why. Is it true that whoever knows your real name is in your inner circle? <bg>

Ricky D. wrote: "Now, why all the secrecy about your identity?......
............................You very nonchalantly bypassed *my* question though!<g>"

Response: Come visit me at

MARS-o-Soft
1010111000111010100010101010^9

}......}...... Epinn, call home ......{......{

Got to go.

Happy Halloween to all!


You very nonchalantly bypassed *my* question though!<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

You sound hitech by using the word "handle." When I see the word "handle,"
I think TINYURL ...... <g>

So, you have a story to tell about your user ID as well. Dyer is a very
legitimate surname.

If I don't hear a "no," I'll call you Rick from now on. Actually, RD has
less keystrokes. Well, let my mood decide.

Epinn

When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!<bg>
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



RD,

Thank you for asking. Sometimes I thought people could read my mind. <g>

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. <g>

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g>

Epinn

Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*.">>>

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob
 
R

RagDyer

What did Shakespeare say:

What's in a name?
A rose is a rose ... or something like that.

Anyway, don't fret, we'll let diversity live on!<bg>

And I must configure my O.E. to enable me to pop up like Jamie did, at the
mere mention of his name or handle.

Regards,

RD
 
R

RagDyer

If that "Come Visit Me At" is some kind of a test,
I've failed miserably!<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
It is amazing that Jamie could hear Bob mention his/her name and showed up
"instantly."

RD, I like handles and you know why. Is it true that whoever knows your
real name is in your inner circle? <bg>

Ricky D. wrote: "Now, why all the secrecy about your identity?......
............................You very nonchalantly bypassed *my* question
though!<g>"

Response: Come visit me at

MARS-o-Soft
1010111000111010100010101010^9

}......}...... Epinn, call home ......{......{

Got to go.

Happy Halloween to all!


You very nonchalantly bypassed *my* question though!<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

You sound hitech by using the word "handle." When I see the word "handle,"
I think TINYURL ...... <g>

So, you have a story to tell about your user ID as well. Dyer is a very
legitimate surname.

If I don't hear a "no," I'll call you Rick from now on. Actually, RD has
less keystrokes. Well, let my mood decide.

Epinn

When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!<bg>
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



RD,

Thank you for asking. Sometimes I thought people could read my mind. <g>

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. <g>

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g>

Epinn

Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*.">>>

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob
 
B

Bob Phillips

I think that whilst Jamie doesn't inhabit anymore, he secretly misses it, so
keeps a watch on :)

Bob
 
E

Epinn

Off Topic - You can ignore.

Bob and RD,

Is it true that we have to type Jamie's full name, Jamie Collins, otherwise he won't "hear" us?
By any other word would smell as sweet."

--From Romeo and Juliet (II, ii, 1-2) <<

I better apologize to those who don't like distraction, before anyone yells at me. From now on, I will include "Off Topic" in the beginning of the post to alert readers. I have seen a MS forum not as focused as this one. ;) I guess it's okay if I don't make this a habit.

Epinn

I think that whilst Jamie doesn't inhabit anymore, he secretly misses it, so
keeps a watch on :)

Bob
 
J

Jamie Collins

Bob said:
I hope that you are well and all is good in ADO/SQL-land.
Click to expand...

Yes thanks, Bob. Belated congratulations on your MVP award (hey, why
doesn't your face show up in photos <g>?)

Jamie.

--
 
J

Jamie Collins

Epinn said:
Is it true that we have to type Jamie's full name, Jamie Collins, otherwise he won't "hear" us?
Click to expand...

This is what I 'hear':

http://groups.google.com/groups/sea...ollins+OR+onedaywhen+group:microsoft.public.*

Via my RSS reader, I still 'listen' to Daily Dose of Excel
(http://www.dicks-blog.com/), otherwise I don't really have time for
Excel: I can spend only a finite time on coffee break. Anyhow, I was in
danger becoming part of the Excel establishment myself (Groucho and
club membership etc).

Jamie.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modifying a complex date formual to count months 4
Peak call times/hours 2
How to enter start and end date/time in excel based on some specific value. 7
Workday Formula to include date 0
Help with If Sum statement - i think.... 4
Sum value between two dates and copy to new cell 6
Time query 9
Find then highlight in yellow 6

Top