Counting unique entries with two given conditions

rogerm · May 13th, 2002, 06:12 AM

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.

Aladin Akyurek · May 13th, 2002, 07:02 AM

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.

Aladin

Shoaib · May 13th, 2002, 07:17 AM

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

megnin · May 15th, 2002, 03:51 PM

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

rogerm · May 16th, 2002, 01:52 AM

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.

Aladin Akyurek · May 16th, 2002, 03:44 AM

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

megnin · May 16th, 2002, 10:31 AM

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.

Aladin Akyurek · May 16th, 2002, 11:00 AM

Quote:

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.

Aladin

Cosmos75 · Oct 14th, 2003, 05:31 PM

******** ******************** ************************************************************************>

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

=

A

B

C

D

1

X

Y

Number or letters where Y > 5 = 6

2

a

1

3

a

2

4

a

6

5

b

6

b

0

7

c

1

8

c

2

9

c

4

10

d

1

11

d

2

12

d

3

13

d

1

14

e

6

15

e

7

16

e

1

17

f

8

18

f

9

19

g

1

20

g

2

21

h

3

22

h

6

23

I

5

24

i

6

25

j

1

26

j

2

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?

Aladin Akyurek · Oct 14th, 2003, 06:31 PM

Quote:

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.

Requires the morefunc add-in.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 06:12 AM	#1
rogerm Board Regular Join Date: May 2002 Location: Bristol, England Posts: 53	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.

May 13th, 2002, 07:02 AM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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. Aladin

May 13th, 2002, 07:17 AM	#3
Shoaib New Member Join Date: Feb 2002 Posts: 6	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

May 15th, 2002, 03:51 PM	#4
megnin Board Regular Join Date: Feb 2002 Location: Fort Lauderdale Posts: 338	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

May 16th, 2002, 01:52 AM	#5
rogerm Board Regular Join Date: May 2002 Location: Bristol, England Posts: 53	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.

May 16th, 2002, 10:31 AM	#7
megnin Board Regular Join Date: Feb 2002 Location: Fort Lauderdale Posts: 338	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.