Enhance sumproduct to return unique employee code count

[Max]

Looking for a way to enhance this sumproduct expression
to return a unique employee code count:

=SUMPRODUCT((Month_of_Occurence>=$B$2)*(Month_of_Occurence<=$B$3)*(Branch=$B10))

Suppose the expression above returns: 5

In a corresponding named range: EmpCode
(range not used in the expression as yet)
the 5 instances returned are due to eg:

Emp1
Emp2
Emp1
Emp3
Emp2

Would like to return a unique employee code count of: 3
for the 5 instances. Thanks.

 

[Bob Phillips]

=COUNT(1/IF((Month_of_Occurence>=$B$2)*(Month_of_Occurence<=$B$3),MATCH(Employees,Employees,0)=ROW(Employees)-MIN(ROW(Employees))+1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Coderre]

Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just
ENTER:

=SUMPRODUCT(--(FREQUENCY(IF((Month_of_Occurence>=$B$2)*
(Month_of_Occurence<=$B$3)*(Branch=$B$10),MATCH(Empcode,Empcode,0)),
ROW(Empcode)-MIN(ROW(Empcode)))>0))

That formula assumes there is a 3rd range named EmpCode.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Max]

Thanks, Ron. That worked swell.

 

[Max]

Bob, thanks.

I adapted your suggestion to suit:

=COUNT(1/IF((Month_of_Occurence>=$B$2)*(Month_of_Occurence<=$B$3)*(Branch=$B10),MATCH(EmpCode,EmpCode,0)=ROW(EmpCode)-MIN(ROW(EmpCode))+1))

and tested it out. Some results were okay but some were out (lower than
actuals). I'm not sure if I had adapted it correctly above?

Ron's suggestion returned correct results.

 

[Ron Coderre]

You're welcome....I'm glad I could help.

--------------------------
Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)