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?
|