how to count only one instance of an item if multiple occurrences
 

Hello,

I have an Excel 2003 spreadsheet. In column C of one of the
worksheets, there are numbers. In some cases, the numbers are unique
and appear only once in the column. For example, the number 348975
appears only once in the column in cell C45. But in other cases, there
are multiple instances of the same number. For example, 123456 appears
in C1, C2, C3, and C4. And 789012 might appear in C10, C150, and C334.

I am hoping someone can help me with this problem. I want to count all
instances of unique numbers in column C AND only one instance of each
number that has multiple instances. In other words, using the numbers
I gave as examples above, I would want to get a result of "3" (a count
of 348975, 123456, and 780912) rather than "8" (a count of C45, C1,
C2, C3, C4, C10, C150, and C334).

One thought I had was to have another column (Q) in the worksheet that
could basically identify each unique number with the number "1" and
then number multiple instance. I could then count only the items with
"1" in column Q. For example

C1 123456 1
C2 123456 2
C3 123456 3
C4 123456 4
C10 789012 1
C45 348975 1
C150 789012 2
C334 789012 3

Thanks in advance for your time and help.

how to count only one instance of an item if multiple occurrences
 

Here is an array formula that will do this.

=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))

Must be array-entered (Ctrl-Shift-Enter). Don't forget to replace
"A2:A10" with your actual range. This formula assumes that there are
only numbers in the range.

You could also used Advanced Filter (DataFilterAdvanced Filter) to
create a list of unique items to another column.

HTH,
JP


On Jan 15, 11:36*am, A Newton wrote:
Hello,

I have an Excel 2003 spreadsheet. In column C of one of the
worksheets, there are numbers. In some cases, the numbers are unique
and appear only once in the column. For example, the number 348975
appears only once in the column in cell C45. But in other cases, there
are multiple instances of the same number. For example, 123456 appears
in C1, C2, C3, and C4. And 789012 might appear in C10, C150, and C334.

I am hoping someone can help me with this problem. I want to count all
instances of unique numbers in column C AND only one instance of each
number that has multiple instances. In other words, using the numbers
I gave as examples above, I would want to get a result of "3" (a count
of 348975, 123456, and 780912) rather than "8" (a count of C45, C1,
C2, C3, C4, C10, C150, and C334).

One thought I had was to have another column (Q) in the worksheet that
could basically identify each unique number with the number "1" and
then number multiple instance. I could then count only the items with
"1" in column Q. For example

C1 * * * * *123456 * * * * *1
C2 * * * * *123456 * * * * *2
C3 * * * * *123456 * * * * *3
C4 * * * * *123456 * * * * *4
C10 * * * * 789012 * * * * 1
C45 * * * * 348975 * * * * 1
C150 * * * *789012 * * * * 2
C334 * * * *789012 * * * * 3

Thanks in advance for your time and help.

how to count only one instance of an item if multiple occurrences
 

On Jan 15, 11:41 am, JP wrote:
Here is an array formula that will do this.

=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))

Must be array-entered (Ctrl-Shift-Enter). Don't forget to replace
"A2:A10" with your actual range. This formula assumes that there are
only numbers in the range.

You could also used Advanced Filter (DataFilterAdvanced Filter) to
create a list of unique items to another column.

HTH,
JP

On Jan 15, 11:36 am, A Newton wrote:

Hello,

I have an Excel 2003 spreadsheet. In column C of one of the
worksheets, there are numbers. In some cases, the numbers are unique
and appear only once in the column. For example, the number 348975
appears only once in the column in cell C45. But in other cases, there
are multiple instances of the same number. For example, 123456 appears
in C1, C2, C3, and C4. And 789012 might appear in C10, C150, and C334.

I am hoping someone can help me with this problem. I want to count all
instances of unique numbers in column C AND only one instance of each
number that has multiple instances. In other words, using the numbers
I gave as examples above, I would want to get a result of "3" (a count
of 348975, 123456, and 780912) rather than "8" (a count of C45, C1,
C2, C3, C4, C10, C150, and C334).

One thought I had was to have another column (Q) in the worksheet that
could basically identify each unique number with the number "1" and
then number multiple instance. I could then count only the items with
"1" in column Q. For example

C1 123456 1
C2 123456 2
C3 123456 3
C4 123456 4
C10 789012 1
C45 348975 1
C150 789012 2
C334 789012 3

Thanks in advance for your time and help.

JP,

Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not
exactly what I need only b/c because my examples were incomplete. My
fault entirely.

In many cases, the numbers in column C might start with a letter (v).
For example:

C1 v123456 1
C2 v123456 2
C3 v123456 3
C4 v123456 4
C10 789012 1
C45 348975 1
C150 789012 2
C334 789012 3
C53 v569802 1
C450 v569802 2

Any ideas?

how to count only one instance of an item if multiple occurrences
 

Hi,

If you havent found the formula to populate cells in col Q based for
unique values in Col C, here's the formula that will do exactly as
you want. You can then simply count the 1s in col Q to get the unique
values in Col C:

In first row of data (say row 1) key in this formula in Col Q:
=COUNTIF($C$1:$C1,C1)

Highlight all the cells in Col Q from row1 to end of data row (say row
450 in your example). Then do a Fill Down (CTRL-D). The important
thing is to give the absolute and relative references exactly as shown
(ie. keying in the $ symbol or leaving it out).

The number of unique values in Col C can now be found by
=COUNTIF($Q$1:$Q$450,1)
in another results cell in the sheet.

This will work if you do it by hand... For putting in a macro (ie.
programming) you need a little more work, but can be done...

Hope this helps.

Best Regards,
-feltra

Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not
exactly what I need only b/c because my examples were incomplete. My
fault entirely.

In many cases, the numbers in column C might start with a letter (v).
For example:

C1 v123456 1
C2 v123456 2
C3 v123456 3
C4 v123456 4
C10 789012 1
C45 348975 1
C150 789012 2
C334 789012 3
C53 v569802 1
C450 v569802 2

Any ideas?

how to count only one instance of an item if multiple occurrences
 

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)0,MATCH(A2:A10,A2 :A10,0),""),
IF(LEN(A2:A10)0,MATCH(A2:A10,A2:A10,0),""))0,1))

Replace "A2:A10" with your range, don't forget to ctrl-shift-enter.


HTH,
JP


On Jan 15, 12:03*pm, A Newton wrote:
On Jan 15, 11:41 am, JP wrote:





Here is an array formula that will do this.

=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))

Must be array-entered (Ctrl-Shift-Enter). Don't forget to replace
"A2:A10" with your actual range. This formula assumes that there are
only numbers in the range.

You could also used Advanced Filter (DataFilterAdvanced Filter) to
create a list of unique items to another column.

HTH,
JP

On Jan 15, 11:36 am, A Newton wrote:

Hello,

I have an Excel 2003 spreadsheet. In column C of one of the
worksheets, there are numbers. In some cases, the numbers are unique
and appear only once in the column. For example, the number 348975
appears only once in the column in cell C45. But in other cases, there
are multiple instances of the same number. For example, 123456 appears
in C1, C2, C3, and C4. And 789012 might appear in C10, C150, and C334.

I am hoping someone can help me with this problem. I want to count all
instances of unique numbers in column C AND only one instance of each
number that has multiple instances. In other words, using the numbers
I gave as examples above, I would want to get a result of "3" (a count
of 348975, 123456, and 780912) rather than "8" (a count of C45, C1,
C2, C3, C4, C10, C150, and C334).

One thought I had was to have another column (Q) in the worksheet that
could basically identify each unique number with the number "1" and
then number multiple instance. I could then count only the items with
"1" in column Q. For example

C1 * * * * *123456 * * * * *1
C2 * * * * *123456 * * * * *2
C3 * * * * *123456 * * * * *3
C4 * * * * *123456 * * * * *4
C10 * * * * 789012 * * * * 1
C45 * * * * 348975 * * * * 1
C150 * * * *789012 * * * * 2
C334 * * * *789012 * * * * 3

Thanks in advance for your time and help.

JP,

Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not
exactly what I need only b/c because my examples were incomplete. My
fault entirely.

In many cases, the numbers in column C might start with a letter (v).
For example:

C1 * * * * *v123456 * * * * *1
C2 * * * * *v123456 * * * * *2
C3 * * * * *v123456 * * * * *3
C4 * * * * *v123456 * * * * *4
C10 * * * * 789012 * * * * 1
C45 * * * * 348975 * * * * 1
C150 * * * *789012 * * * * 2
C334 * * * *789012 * * * * 3
C53 * * * *v569802 * * * * 1
C450 * * *v569802 * * * * 2

Any ideas?- Hide quoted text -

- Show quoted text -

how to count only one instance of an item if multiple occurrences
 

On Jan 15, 1:54 pm, feltra wrote:
Hi,

If you havent found the formula to populate cells in col Q based for
unique values in Col C, here's the formula that will do exactly as
you want. You can then simply count the 1s in col Q to get the unique
values in Col C:

In first row of data (say row 1) key in this formula in Col Q:
=COUNTIF($C$1:$C1,C1)

Highlight all the cells in Col Q from row1 to end of data row (say row
450 in your example). Then do a Fill Down (CTRL-D). The important
thing is to give the absolute and relative references exactly as shown
(ie. keying in the $ symbol or leaving it out).

The number of unique values in Col C can now be found by
=COUNTIF($Q$1:$Q$450,1)
in another results cell in the sheet.

This will work if you do it by hand... For putting in a macro (ie.
programming) you need a little more work, but can be done...

Hope this helps.

Best Regards,
-feltra

Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not
exactly what I need only b/c because my examples were incomplete. My
fault entirely.

In many cases, the numbers in column C might start with a letter (v).
For example:

C1 v123456 1
C2 v123456 2
C3 v123456 3
C4 v123456 4
C10 789012 1
C45 348975 1
C150 789012 2
C334 789012 3
C53 v569802 1
C450 v569802 2

Any ideas?

Thanks! Great suggestion. That worked like a charm. All items in
column Q are now appropriately numbered.

One follow-up --

On another worksheet -- worksheet 2 -- I now want to tally all the
items that have a value of 1 in column Q and that also have a value of
"foo" in column A and "bar" in column M on worksheet 1. I thought I
could find all the items that I was looking for with this formula:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet'!$Q$2:$Q$450="1"))

but it is returning "0" whereas I know from looking at worksheet 1
that I should actually be getting a value of "3".

This:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet'!$Q$2:$Q$450="1"))

returns "6", which counts all instances without factoring in a value
of "1" in column Q.

Ideas?

how to count only one instance of an item if multiple occurrences
 

On Jan 15, 3:14 pm, A Newton wrote:
On Jan 15, 1:54 pm, feltra wrote:



Hi,

If you havent found the formula to populate cells in col Q based for
unique values in Col C, here's the formula that will do exactly as
you want. You can then simply count the 1s in col Q to get the unique
values in Col C:

In first row of data (say row 1) key in this formula in Col Q:
=COUNTIF($C$1:$C1,C1)

Highlight all the cells in Col Q from row1 to end of data row (say row
450 in your example). Then do a Fill Down (CTRL-D). The important
thing is to give the absolute and relative references exactly as shown
(ie. keying in the $ symbol or leaving it out).

The number of unique values in Col C can now be found by
=COUNTIF($Q$1:$Q$450,1)
in another results cell in the sheet.

This will work if you do it by hand... For putting in a macro (ie.
programming) you need a little more work, but can be done...

Hope this helps.

Best Regards,
-feltra

Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not
exactly what I need only b/c because my examples were incomplete. My
fault entirely.

In many cases, the numbers in column C might start with a letter (v).
For example:

C1 v123456 1
C2 v123456 2
C3 v123456 3
C4 v123456 4
C10 789012 1
C45 348975 1
C150 789012 2
C334 789012 3
C53 v569802 1
C450 v569802 2

Any ideas?

Thanks! Great suggestion. That worked like a charm. All items in
column Q are now appropriately numbered.

One follow-up --

On another worksheet -- worksheet 2 -- I now want to tally all the
items that have a value of 1 in column Q and that also have a value of
"foo" in column A and "bar" in column M on worksheet 1. I thought I
could find all the items that I was looking for with this formula:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet'!$Q$2:$Q$450="1"))

but it is returning "0" whereas I know from looking at worksheet 1
that I should actually be getting a value of "3".

This:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet'!$Q$2:$Q$450="1"))

returns "6", which counts all instances without factoring in a value
of "1" in column Q.

Ideas?

There was a typo in my previous examples. They should be:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet 1'!$Q$2:$Q$450="1"))

and

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet 1'!$Q$2:$Q$450="1"))

how to count only one instance of an item if multiple occurrences
 

On Jan 15, 3:53 pm, A Newton wrote:
On Jan 15, 3:14 pm, A Newton wrote:



On Jan 15, 1:54 pm, feltra wrote:

Hi,

If you havent found the formula to populate cells in col Q based for
unique values in Col C, here's the formula that will do exactly as
you want. You can then simply count the 1s in col Q to get the unique
values in Col C:

In first row of data (say row 1) key in this formula in Col Q:
=COUNTIF($C$1:$C1,C1)

Highlight all the cells in Col Q from row1 to end of data row (say row
450 in your example). Then do a Fill Down (CTRL-D). The important
thing is to give the absolute and relative references exactly as shown
(ie. keying in the $ symbol or leaving it out).

The number of unique values in Col C can now be found by
=COUNTIF($Q$1:$Q$450,1)
in another results cell in the sheet.

This will work if you do it by hand... For putting in a macro (ie.
programming) you need a little more work, but can be done...

Hope this helps.

Best Regards,
-feltra

Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not
exactly what I need only b/c because my examples were incomplete. My
fault entirely.

In many cases, the numbers in column C might start with a letter (v).
For example:

C1 v123456 1
C2 v123456 2
C3 v123456 3
C4 v123456 4
C10 789012 1
C45 348975 1
C150 789012 2
C334 789012 3
C53 v569802 1
C450 v569802 2

Any ideas?

Thanks! Great suggestion. That worked like a charm. All items in
column Q are now appropriately numbered.

One follow-up --

On another worksheet -- worksheet 2 -- I now want to tally all the
items that have a value of 1 in column Q and that also have a value of
"foo" in column A and "bar" in column M on worksheet 1. I thought I
could find all the items that I was looking for with this formula:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet'!$Q$2:$Q$450="1"))

but it is returning "0" whereas I know from looking at worksheet 1
that I should actually be getting a value of "3".

This:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet'!$Q$2:$Q$450="1"))

returns "6", which counts all instances without factoring in a value
of "1" in column Q.

Ideas?

There was a typo in my previous examples. They should be:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet 1'!$Q$2:$Q$450="1"))

and

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet 1'!$Q$2:$Q$450="1"))

Issue solved. . .

I removed the double-quotes from around the "1" like so:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet 1'!$Q$2:$Q$450=1))