sum unique values in a variable range

[Acid-Sky]

Hi, this problem is driving me nuts:

- I have an external data source containing start date, end date, working hours, travel hours, holidays, etc sorted by project and engineer
- i need to calculate average engineer productivity

I got so far:
This gives me the number of engineers:
SUM(IF(FREQUENCY(IF(LEN(Master!I2:I100)>0,MATCH(Master!I2:I100,Master!I2:I100,0),""),IF(LEN(Master!I2:I100)>0,MATCH(Master!I2:I100,Master!I2:I100,0),""))>0,1))

And this the overall working times in one period (selectable):
=SUMPRODUCT(--(Master!D2:D100>=KPI!G2),--(Master!E2:E100<KPI!H2),F10)

KPI!G2 and H2 are dates.

Now... as the number of engineers is changing I would need the number of unique engineers within this selected period.

How do I do that? I tried to combine the formulas like crazy but it just doesn't work. Due to the nature of the data source I cannot add named ranges or similar... it should be done by a "simple" formula.

Any ideas?

Worst case scnario would be to use VBA but somehow I am sure there is a solution using formulas only.

 

[Acid-Sky]

sorry, forgot to mention... the formula to get the number of engineers is an array formula so CTRL+SHIFT+ENTER needed to make it work.