Counting Unique Records with multiple conditions

[Keithlearn]

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$60000=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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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$60000=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

[Domenic]

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 <[email protected]>,
"Keithlearn" <[email protected]> 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$60000=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

[Harlan Grove]

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.

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>