Thanks:  0
Likes:  0

# Thread: Counting unique entries with two given conditions

1. Could anyone help me with the following:

I have three colums of data.

In the first colum I have names
The second Column I have week numbers
And the 3rd column I have dates

I have to report how many dates in ta given week number reports were submitted by each person.

The problem is that on some days more than one report has been submitted so when I count using an array formula such as

=SUM((A2:A10="Name")*(B2:B10="week number")
*C2:C10)
The result is wrong.

I need to count the number of distinct (unique) dates in the date column (c)for a given person on a given date

so I need a combination of

=SUM((A2:A10="Name")*(B2:B10="week number")
to select the ranges together with

=SUM(N(FREQUENCY(C1:A10,C1:C10)>0))

but I'm not sure how to put them together.

to count the unique entries for the range.

Or perhaps there is an easier way or another function I could use.

Any suggestions would be gratefully received.

2. Array-enter:

=SUM(IF((A2:A5=E1)*(B2:B5=F1),1/COUNTIF(C2:C5,C2:C5)))

where E1 houses a name and F1 a week number.

To array-enter a formula, hit control+shift+enter at the same time, not just enter.

3. Why dont you use a pivot table?

Name and week in row area, date in data field
will give you the info in an instant

4. I have a similar problem. I tried it with a pivit table. It did not give me unique "dates", but counted every occurance.
How do you tell a pivit table to only count unique occurances?
"Count of" counts them all.

megnin@nortelnetworks.com

5. The first solution didn't seem to work - It still counted all the values even though I formatted the dates/time as just a short date. It perhaps only works on whol numbers.

I finally solved the problem using some code and a pivot table. (Thanks for suggesting this I hadnt used pivot tables before)

As the du-plicates when they occurred were appearing one after the other I used the following code to replace all the duplicate entries, except the first, in each group with the string "----". To use this code, first select the cells (in one column) that you want to change, then run the procedure.

Sub FixDuplicateRows()
Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
Cells(RowNdx, ColNum).Value = "----"
End If
Next RowNdx
End Sub

(I got this code from http://www.cpearson.com/excel/duplicat.htm>

This formed a column with just the unique dates for each week. I then used this column in the pivot table which counted the dates for me.

6. On 2002-05-16 00:52, rogerm wrote:
The first solution didn't seem to work - It still counted all the values even though I formatted the dates/time as just a short date. It perhaps only works on whol numbers.

Here is how:

 Microsoft Excel - aaMultCondUniqCount RogerM.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp G2 = =SUM(IF((A2:A5=E2)*(B2:B5=F2),1/COUNTIF(C2:C5,C2:C5))) A B C D E F G 1 Name Week Num Date 2 ann :alert('=WEEKNUM(C2)')>1 1/2/2002 ann 1 :alert('{=SUM(IF((A2:A5=E2)*(B2:B5=F2),1/COUNTIF(C2:C5,C2:C5)))}')>1 3 ann :alert('=WEEKNUM(C3)')>1 1/2/2002 4 bob :alert('=WEEKNUM(C4)')>1 1/4/2002 5 fred :alert('=WEEKNUM(C5)')>2 1/7/2002 Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

7. My result using the above formul is a fraction. The count of unique items could not be a fraction. I have a worksheet with 42000 rows. I get 901.8174 as the result.

Order#........Color........Shape
11A Red Circle
11A Red Square
11A Blue Square
12B Blue Triangle
12B Red Rectangle
13C Green Circle
13C Green Triangle

I need a formula that will return the number of unique Order #s that contain only red circles or squares and blue circles and squares. The result from the above data would be 1. Only 11A..Red..Circle is unique Order # matching criteria.

I do not think it can be done with a single formula.

(Advanced Filters do not return unique numbers, they return every matching Order #)

Thanks for any assistance.

8. On 2002-05-16 09:31, megnin wrote:
My result using the above formul is a fraction. The count of unique items could not be a fraction. I have a worksheet with 42000 rows. I get 901.8174 as the result.

Order#........Color........Shape
11A Red Circle
11A Red Square
11A Blue Square
12B Blue Triangle
12B Red Rectangle
13C Green Circle
13C Green Triangle

I need a formula that will return the number of unique Order #s that contain only red circles or squares and blue circles and squares. The result from the above data would be 1. Only 11A..Red..Circle is unique Order # matching criteria.

I do not think it can be done with a single formula.

(Advanced Filters do not return unique numbers, they return every matching Order #)

Thanks for any assistance.
Megnin,

The above formula is appropriate in Rogerm's case that requires AND'ing. Your case requires OR'ing (and AND'ing) for which it will not work. Becasuse you have a huge amount data, a single array formula (if there is one0 would be performance-degrading. That's reason why I looked and suggested a different solution for your case in a different thread.

9. ## Re: Counting unique entries with two given conditions

******** ******************** ************************************************************************>
 Microsoft Excel - Book3 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 C26 =

A
B
C
D
1
XYNumber or letters where Y > 5 = 6
2
a1
3
a2
4
a6
5
b6
6
b0
7
c1
8
c2
9
c4
10
d1
11
d2
12
d3
13
d1
14
e6
15
e7
16
e1
17
f8
18
f9
19
g1
20
g2
21
h3
22
h6
23
I5
24
i6
25
j1
26
j2
 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

How can I count the number of Unique X-Values that have at least one Y-value greater than 5?

10. ## Re: Counting unique entries with two given conditions

Originally Posted by Cosmos75
...
How can I count the number of Unique X-Values that have at least one Y-value greater than 5?
=COUNTDIFF(IF(B2:B26>5,A2:A26))-1

which you need to confirm with control+shift+enter, not just with enter.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•