# Help with SUMPRODUCT()

#### fayez_MrExcel

##### Active Member
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
=SUMPRODUCT(--(C2:C1000="Active File"),--(D2:D1000="HR"),--(LEFT(A2:A1000,1)="5"))

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

Neil I think you mean:

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

Richard - well spotted!

=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.

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

Replies
8
Views
1K
Replies
17
Views
659
Replies
2
Views
342
Replies
7
Views
678
Replies
5
Views
415

1,220,977
Messages
6,157,179
Members
451,402
Latest member
Lyv

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back