Counting Unique Records with multiple conditions
 

I have been racking my brain trying to count unique records within a table
with two other conditions. An example of the table is as follows:

EE ID Week # Location
100417 16 BRY
100417 16 BRY
100213 16 BRY
100427 16 BRY
100428 16 BRY
100415 16 BRY
100015 16 BRY
100015 16 BRY
100015 16 BRY
100151 16 BRY
100151 16 BRY

If I was counting unique EE ID's, that match week 16 and location BRY, the
answer would be 7. The table I am using is huge as I have approx 1000 lines
per week with multiple locations.

I have tried the following formula, but get a n/a result (week # is in
column D, Location is in column G and EE ID is in column A)

=SUM(IF(Data!$G$2:$G$60000="BRY",IF(Data!$D$2:$D$6 0000=16,FREQUENCY(MATCH(Data!$A$2:$A$60000,Data!$A $2:$A$60000,0),MATCH(Data!$A$2:$A$60000,Data!$A$2: $A$60000,0)0),1),0))

Any help would be greatly appreciated.

Keith

Hi Keith,

How about

=SUMPRODUCT(((B2:B20=16)*(C2:C20="BRY")*(A2:A20<" "))/COUNTIF(A2:A20,A2:A20&
""))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Keithlearn" wrote in message
...
I have been racking my brain trying to count unique records within a table
with two other conditions. An example of the table is as follows:

EE ID Week # Location
100417 16 BRY
100417 16 BRY
100213 16 BRY
100427 16 BRY
100428 16 BRY
100415 16 BRY
100015 16 BRY
100015 16 BRY
100015 16 BRY
100151 16 BRY
100151 16 BRY

If I was counting unique EE ID's, that match week 16 and location BRY, the
answer would be 7. The table I am using is huge as I have approx 1000
lines
per week with multiple locations.

I have tried the following formula, but get a n/a result (week # is in
column D, Location is in column G and EE ID is in column A)


=SUM(IF(Data!$G$2:$G$60000="BRY",IF(Data!$D$2:$D$6 0000=16,FREQUENCY(MATCH(Da
ta!$A$2:$A$60000,Data!$A$2:$A$60000,0),MATCH(Data! $A$2:$A$60000,Data!$A$2:$A
$60000,0)0),1),0))

Any help would be greatly appreciated.

Keith

If Column A contains numerical values, try...

=SUM(IF(FREQUENCY(IF((A2:A12<"")*(D2:D12=16)*(G2: G12="BRY"),A2:A12),IF((
A2:A12<"")*(D2:D12=16)*(G2:G12="BRY"),A2:A12))0, 1))

If Column A contains text values, try...

=SUM(IF(FREQUENCY(IF((A2:A12<"")*(D2:D12=16)*(G2: G12="BRY"),MATCH(A2:A12
,A2:A12,0)),ROW(INDIRECT("1:"&ROWS(A2:A12))))0,1) )

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
just ENTER.

Hope this helps!

In article ,
"Keithlearn" wrote:

I have been racking my brain trying to count unique records within a table
with two other conditions. An example of the table is as follows:

EE ID Week # Location
100417 16 BRY
100417 16 BRY
100213 16 BRY
100427 16 BRY
100428 16 BRY
100415 16 BRY
100015 16 BRY
100015 16 BRY
100015 16 BRY
100151 16 BRY
100151 16 BRY

If I was counting unique EE ID's, that match week 16 and location BRY, the
answer would be 7. The table I am using is huge as I have approx 1000 lines
per week with multiple locations.

I have tried the following formula, but get a n/a result (week # is in
column D, Location is in column G and EE ID is in column A)

=SUM(IF(Data!$G$2:$G$60000="BRY",IF(Data!$D$2:$D$6 0000=16,FREQUENCY(MATCH(Data
!$A$2:$A$60000,Data!$A$2:$A$60000,0),MATCH(Data!$A $2:$A$60000,Data!$A$2:$A$600
00,0)0),1),0))

Any help would be greatly appreciated.

Keith

Bob Phillips wrote...
How about

=SUMPRODUCT(((B2:B20=16)*(C2:C20="BRY")*(A2:A20< ""))
/COUNTIF(A2:A20,A2:A20&""))
....

Doesn't work. OP's sample data was too easy - no variation in criteria
fields. Using the OP's sample data, and adjusting your formula to refer
to rows 2 to 12, change the first Week# field for EE ID 100015 to 22.
Your formula then returns 6.667, which is a clear error (counts are
always integers).

The problem is that you can't use COUNTIF in the denominator because
it'll count *ALL* instances of each value in A2:A12.

You gotta get tricky, and the OP was closer than he (or you) thought.

=COUNT(1/FREQUENCY((B2:B12=16)*(C2:C12="BRY")*MATCH(A2:A12, A2:A12,0),
ROW(INDIRECT("1:"&ROWS(B2:B12)))-1))
-(SUMPRODUCT((A1:A12=A15)*(C1:C12=B15)-1)0)

The trick is putting the criteria *inside* the FREQUENCY call.
Subtracting 1 from ROW()'s result creates a 0 bin for the records that
don't satisfy the criteria. The final SUMPRODUCT term excludes the 0
bin if there are any records that don't satisfy the criteria. Also note
that this doesn't have to be entered as an array formula.

Hi Harlan,

Yeah, I figured that myself afterwards. Worked at start and with OPs data,
but fell apart when I tried to make it generic to save in the library - by
which time, Domenic had posted.

Bob


"Harlan Grove" wrote in message
oups.com...
Bob Phillips wrote...
How about

=SUMPRODUCT(((B2:B20=16)*(C2:C20="BRY")*(A2:A20< ""))
/COUNTIF(A2:A20,A2:A20&""))
...

Doesn't work. OP's sample data was too easy - no variation in criteria
fields. Using the OP's sample data, and adjusting your formula to refer
to rows 2 to 12, change the first Week# field for EE ID 100015 to 22.
Your formula then returns 6.667, which is a clear error (counts are
always integers).

The problem is that you can't use COUNTIF in the denominator because
it'll count *ALL* instances of each value in A2:A12.

You gotta get tricky, and the OP was closer than he (or you) thought.

=COUNT(1/FREQUENCY((B2:B12=16)*(C2:C12="BRY")*MATCH(A2:A12, A2:A12,0),
ROW(INDIRECT("1:"&ROWS(B2:B12)))-1))
-(SUMPRODUCT((A1:A12=A15)*(C1:C12=B15)-1)0)

The trick is putting the criteria *inside* the FREQUENCY call.
Subtracting 1 from ROW()'s result creates a 0 bin for the records that
don't satisfy the criteria. The final SUMPRODUCT term excludes the 0
bin if there are any records that don't satisfy the criteria. Also note
that this doesn't have to be entered as an array formula.