Help with SUMPRODUCT()

[fayez_MrExcel]

How to Count the number of employee that is "Active", under "HR" & employee number that starts with 1(employee no. is 5 digits). My sheet has: A2-Employee Number, B2-Employee Name, C2-Status (Active or Inactive), D2-Dept Name. I have already this syntax and I want to add the last criteria which is to include in counting if the employee number starts with 1. This is the syntax i used: =SUMPRODUCT(--(C2:C1000="Active File"),--(D2:D1000="HR")). Thanks

 

[njimack]

=SUMPRODUCT(--(C2:C1000="Active File"),--(D2:D1000="HR"),--(LEFT(A2:A1000,1)="5"))

 

[Richard Schollar]

=SUMPRODUCT(--(C2:C1000="Active File"),--(D2:D1000="HR"),--(LEFT(A2:A1000,1)="5"))

Neil I think you mean:

=SUMPRODUCT(--(C2:C1000="Active File"),--(D2:D1000="HR"),--(LEFT(A2:A1000,1)="1"))

 

[njimack]

Richard - well spotted!

 

[Stormseed]

=SUMPRODUCT(--(C2:C1000="Active File"),--(D2:D1000="HR"),--(LEFT(A2:A1000,1)="5"))

The day when I opened MS-Excel for the first time, around 4 years back, I learnt something new. The coincidence is...I am still learning

thanks, Neil.

 

[fayez_MrExcel]

You guys are realy 'pro' thanks a lot ...