Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > counting values ignoring duplicates
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 60
Default counting values ignoring duplicates
Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 35,218
Default counting values ignoring duplicates
You can use a formula like:
=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
to count the number of unique entries in A1:A10.

You may want to look at Chip Pearson's site. He has lots of techniques to work
with duplicates:
http://www.cpearson.com/excel/Duplicates.aspx


matt3542 wrote:

Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 60
Default counting values ignoring duplicates
Thanks very much, that worked perfectly

"Dave Peterson" wrote:

You can use a formula like:
=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
to count the number of unique entries in A1:A10.

You may want to look at Chip Pearson's site. He has lots of techniques to work
with duplicates:
http://www.cpearson.com/excel/Duplicates.aspx


matt3542 wrote:

Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049

--

Dave Peterson

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 896
Default counting values ignoring duplicates
http://www.cpearson.com/Excel/Duplicates.aspx
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 60
Default counting values ignoring duplicates
Thanks for the link, appreciated, will undoubtedly come in handy for future
queries

"Jarek Kujawa" wrote:

http://www.cpearson.com/Excel/Duplicates.aspx

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 10,593
Default counting values ignoring duplicates
=SUMPRODUCT((A2:A20<"")/COUNTIF(A2:A20,A2:A20&""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"matt3542" wrote in message
...
Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would
expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 60
Default counting values ignoring duplicates
Hi Bob, this also worked, many thanks, appreciated

"Bob Phillips" wrote:

=SUMPRODUCT((A2:A20<"")/COUNTIF(A2:A20,A2:A20&""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"matt3542" wrote in message
...
Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would
expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049




Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 913
Default counting values ignoring duplicates
On Mon, 14 Jul 2008 04:17:02 -0700, matt3542
wrote:

Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049


Try this formula:

=SUM(1/COUNTIF(A2:A217,A2:A217))

Hope this helps / Lars-Åke
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,836
Default counting values ignoring duplicates
I can come up with at least 9 ways to do this; any more is moot:
=SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78)+(A1:A78="")))
=SUM(IF(FREQUENCY(IF(LEN(A1:A971)0,MATCH(A1:A971, A1:A971,0),""),IF(LEN(A1:A971)0,MATCH(A1:A971,A1: A971,0),""))0,1))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(IF(A1:A400<"",1/COUNTIF(A1:A400,A1:A400)))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78&"")))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(--(FREQUENCY(IF(A1:A2676<"",MATCH(A1:A2676,A1:A2676 ,0)),ROW(INDIRECT("1:"&ROWS(A1:A2676))))0))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)

Regards,
Ryan---

--
RyGuy


"Lars-Ã…ke Aspelin" wrote:

On Mon, 14 Jul 2008 04:17:02 -0700, matt3542
wrote:

Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049


Try this formula:

=SUM(1/COUNTIF(A2:A217,A2:A217))

Hope this helps / Lars-Ã…ke

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 60
Default counting values ignoring duplicates
Wow, thanks for taking the time to give me so many options, this has been
incredibly helpful!

"ryguy7272" wrote:

I can come up with at least 9 ways to do this; any more is moot:
=SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78)+(A1:A78="")))
=SUM(IF(FREQUENCY(IF(LEN(A1:A971)0,MATCH(A1:A971, A1:A971,0),""),IF(LEN(A1:A971)0,MATCH(A1:A971,A1: A971,0),""))0,1))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(IF(A1:A400<"",1/COUNTIF(A1:A400,A1:A400)))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78&"")))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(--(FREQUENCY(IF(A1:A2676<"",MATCH(A1:A2676,A1:A2676 ,0)),ROW(INDIRECT("1:"&ROWS(A1:A2676))))0))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)

Regards,
Ryan---

--
RyGuy


"Lars-Ã…ke Aspelin" wrote:

On Mon, 14 Jul 2008 04:17:02 -0700, matt3542
wrote:

Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049


Try this formula:

=SUM(1/COUNTIF(A2:A217,A2:A217))

Hope this helps / Lars-Ã…ke

Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,836
Default counting values ignoring duplicates
I think it's great to have a few options for doing this...just to confirm
that your results are correct...


Regards,
Ryan---

--
RyGuy


"matt3542" wrote:

Wow, thanks for taking the time to give me so many options, this has been
incredibly helpful!

"ryguy7272" wrote:

I can come up with at least 9 ways to do this; any more is moot:
=SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78)+(A1:A78="")))
=SUM(IF(FREQUENCY(IF(LEN(A1:A971)0,MATCH(A1:A971, A1:A971,0),""),IF(LEN(A1:A971)0,MATCH(A1:A971,A1: A971,0),""))0,1))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(IF(A1:A400<"",1/COUNTIF(A1:A400,A1:A400)))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78&"")))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(--(FREQUENCY(IF(A1:A2676<"",MATCH(A1:A2676,A1:A2676 ,0)),ROW(INDIRECT("1:"&ROWS(A1:A2676))))0))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)

Regards,
Ryan---

--
RyGuy


"Lars-Ã…ke Aspelin" wrote:

On Mon, 14 Jul 2008 04:17:02 -0700, matt3542
wrote:

Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049


Try this formula:

=SUM(1/COUNTIF(A2:A217,A2:A217))

Hope this helps / Lars-Ã…ke

Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 60
Default counting values ignoring duplicates
Hi Ryan, apologies for the delay replying, just to confirm all 5 solutions
worked perfectly, clever stuff, thanks so much again
Regards
Matt

"ryguy7272" wrote:

I think it's great to have a few options for doing this...just to confirm
that your results are correct...


Regards,
Ryan---

--
RyGuy


"matt3542" wrote:

Wow, thanks for taking the time to give me so many options, this has been
incredibly helpful!

"ryguy7272" wrote:

I can come up with at least 9 ways to do this; any more is moot:
=SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78)+(A1:A78="")))
=SUM(IF(FREQUENCY(IF(LEN(A1:A971)0,MATCH(A1:A971, A1:A971,0),""),IF(LEN(A1:A971)0,MATCH(A1:A971,A1: A971,0),""))0,1))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(IF(A1:A400<"",1/COUNTIF(A1:A400,A1:A400)))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78&"")))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(--(FREQUENCY(IF(A1:A2676<"",MATCH(A1:A2676,A1:A2676 ,0)),ROW(INDIRECT("1:"&ROWS(A1:A2676))))0))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)

Regards,
Ryan---

--
RyGuy


"Lars-Ã…ke Aspelin" wrote:

On Mon, 14 Jul 2008 04:17:02 -0700, matt3542
wrote:

Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049


Try this formula:

=SUM(1/COUNTIF(A2:A217,A2:A217))

Hope this helps / Lars-Ã…ke

Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 273
Default counting values ignoring duplicates
Just curious:
Which of your 9 suggested solutions use the least computing power and
computes the fastest?

"ryguy7272" wrote:

I can come up with at least 9 ways to do this; any more is moot:
=SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78)+(A1:A78="")))
=SUM(IF(FREQUENCY(IF(LEN(A1:A971)0,MATCH(A1:A971, A1:A971,0),""),IF(LEN(A1:A971)0,MATCH(A1:A971,A1: A971,0),""))0,1))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(IF(A1:A400<"",1/COUNTIF(A1:A400,A1:A400)))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78&"")))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(--(FREQUENCY(IF(A1:A2676<"",MATCH(A1:A2676,A1:A2676 ,0)),ROW(INDIRECT("1:"&ROWS(A1:A2676))))0))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)

Regards,
Ryan---

--
RyGuy


"Lars-Ã…ke Aspelin" wrote:

On Mon, 14 Jul 2008 04:17:02 -0700, matt3542
wrote:

Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049


Try this formula:

=SUM(1/COUNTIF(A2:A217,A2:A217))

Hope this helps / Lars-Ã…ke

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

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
counting the cells ignoring zero values awais Excel Discussion (Misc queries) 2 June 14th 08 11:37 PM
Counting Unique Values with Duplicates DOUG ECKERT[_2_] Excel Discussion (Misc queries) 0 May 8th 08 03:00 PM
counting function but not double counting duplicates JRD Excel Worksheet Functions 2 November 7th 07 06:43 PM
Counting Names in a Column, Ignoring Duplicates Bruce Excel Discussion (Misc queries) 8 March 18th 07 01:01 PM
average values in non-contiguous cells, ignoring 0 values RWormdahl Excel Worksheet Functions 3 October 30th 06 01:06 AM


All times are GMT +1. The time now is 07:22 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement