Counting unique entries with two given conditions

rogerm

Board Regular
Joined
May 12, 2002
Messages
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

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:

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=8><FONT COLOR=WHITE>Microsoft Excel - aaMultCondUniqCount RogerM.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=8>(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>G2</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=5 BGCOLOR=White>=SUM(IF((A2:A5=E2)*(B2:B5=F2),1/COUNTIF(C2:C5,C2:C5)))</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>F</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>G</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Name</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Week Num</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Date</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>ann</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=WEEKNUM(C2)')><FONT FACE=Arial COLOR=#000000>1</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1/2/2002</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>ann</FONT></TD><TD BGCOLOR=#FFFF00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1</FONT></TD><TD BGCOLOR=#CCFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('{=SUM(IF((A2:A5=E2)*(B2:B5=F2),1/COUNTIF(C2:C5,C2:C5)))}')><FONT FACE=Arial COLOR=#000000>1</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>ann</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=WEEKNUM(C3)')><FONT FACE=Arial COLOR=#000000>1</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1/2/2002</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>bob</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=WEEKNUM(C4)')><FONT FACE=Arial COLOR=#000000>1</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1/4/2002</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>fred</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=WEEKNUM(C5)')><FONT FACE=Arial COLOR=#000000>2</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1/7/2002</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=8><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>To see the formula in the cells just click on the cells hyperlink</FONT>

<FONT COLOR=#339966 SIZE=1>The above image was automatically generated by [HtmlMaker V1.22]</FONT>
<FONT COLOR=#339966 SIZE=1>If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you.</FONT>
<FONT COLOR=#339966 SIZE=1>This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo</FONT>
</CENTER>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Book3
ABCD
1XYNumber or letters where Y > 5 = 6
2a1
3a2
4a6
5b6
6b0
7c1
8c2
9c4
10d1
11d2
12d3
13d1
14e6
15e7
16e1
17f8
18f9
19g1
20g2
21h3
22h6
23I5
24i6
25j1
26j2
Sheet1

How can I count the number of Unique X-Values that have at least one Y-value greater than 5?
 
Upvote 0
Cosmos75 said:
...
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top