Counting Unique values with multiple criteria

[fayez_MrExcel]

Im trying to count a unique value (i.e. the employee no. with 2 criteria: first is the job category (i.e in E3) and the other employee type (i.e. in F3), my below formula is only for criteria. Im trying to put "AND" but the result is wrong. Pls assist to add the second criteria which is employee type (i.e. in F3). Thanks

=SUM(--(FREQUENCY(IF(TC_JobCategory=E3,MATCH(TC_EmpNo,TC_EmpNo,0)),ROW(TC_EmpNo)-ROW(E3)+1)>0))

 

[mamady]

Hi Dear,

Try the following (adjust where required)
=SUM(--(FREQUENCY(IF(TC_JobCategory=E3,MATCH(TC_EmpNo&EmpType,TC_EmpNo&EmpType,0)),ROW(TC_EmpNo)-ROW(E3)+1)>0))

 

[fayez_MrExcel]

Thanks but how can i reference the value of Emp Type in in F3

 

[mamady]

Can you please share a sample sheet or a screenshot, it would be easier for me to suggest the amendments to your working formula

Regards

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

For the sample suggested by mamady it would be good to use XL2BB to make it easier for helpers by not having to manually type out sample data to test with.