Unique values depending on variable

[Skybeau]

Hi guys,

I have a problem I can't solve. I have a workbook that has two sheets on it.

In Sheet 1, cell A2 I have a variable, in this case it's a number 249

In Sheet 2, I have an annual leave report which shows entries for colleagues with annual leave booked in the future. Due to the fact that a colleague could book more than 1 lot of leave, I'd like the number of unique employee numbers from the variable in Sheet 1, Cell A2.

The variable in Sheet 1, Cell A2 will be looking for corresponding data from Sheet 2, Column I. The employee numbers are located in Sheet 2, Column A.

Is this possible ?


Sheet 2 Data

ColA Col I

68917 John Daly 249
12249 Tiger Woods 249
68917 John Daly 249
12249 Tiger Woods 249
2262 Bill McGrath 249
68917 John Daly 249

[JBeaucaire]

Try this:

=SUM(INDEX((Sheet2!$I$1:$I$7=A6) * (MATCH(Sheet2!$A$1:$A$7, Sheet2!$A$1:$A$7, 0) = ROW($1:$7)), 0))

The ranges have to match your data range exactly, so adjust them accordingly.

[DonkeyOte]

JB, I would make the point that using INDEX in this form is generally regarded as being pretty slow - else we'd use all the time in preference to SUMPRODUCT.

I think also that the above would fail to return correct result if the value in A appeared multiple times but under different codes (ie the MATCH should be conducted against the concatenation of I & A rather than just A) ... I believe also that should any entries in A be blank on Sheet2 then you would get an #N/A error.

Adopting JB's approach in SUMPRODUCT form with the above taken into account I believe something along the lines of:

=SUMPRODUCT(--(Sheet2!$A$1:$A$7<>""),--(Sheet2!$I$1:$I$7=A6),--(MATCH(Sheet2!$I$1:$I$7&Sheet2!$A$1:$A$7&"",Sheet2!$I$1:$I$7&Sheet2!$A$1:$A$7&"",0)=ROW(Sheet2!$A$1:$A$7)))

should work... it would of course be easier to have a concatenation column at source, eg:

Sheet2!J1: =$A1&":"&$I1
copied down

Then

=SUMPRODUCT(--(Sheet2!$A$1:$A$7<>""),--(Sheet2!$I$1:$I$7=A6),1/COUNTIF(Sheet2!$J$1:$J$7,Sheet2!$J$1:$J$7&""))

which is as you can see a little less convoluted... but not much