Sum if two varialbles (sum if and if)

  • Thread starter Thread starter Marco
  • Start date Start date
M

Marco

Hello. I already search some post about this but I didn't understood.

I have a sheet with 3 columns: EmployeeNumber; Name; AdmissionDate

In another sheet I have another 3 columns: EmployeeNumber; NumberOfHours; Date

I need to sum in sheet one all hours that are in sheet two, but I need
something like: Sum if EmployeeNumber=EmployeeNumber and Date >=AdmissionDate

So, in resume I need to sum all hours from an employee that are equal or
bigger is AdmissionDate

I have Excel 2003 and 2007, so ......

Please Help.

Regards,
Marco
 
=SUM(IF(EmployeeNumber=EmployeeNumber)*(Date>=AdmissionDate),NumberOfHours,))

array-enter it = CTRL+SHIFT+ENTER
 
Assuming sheet names of Sheet1 and Sheet2, and the columns of data are A
through C for both sheets. And assuming your sheet1 totals begin in row 2.

=SUMPRODUCT(--(Sheet2!$A$2:$A$100=A2),--(Sheet2!$C$2:$C$100>=C2),--(Sheet2!$B$2:$B$100))

I just used 100 rows, but if you need more, feel free, just ensure that all
references are the same length in rows!
 
=SUMPRODUCT(--(emp_num_cell=Sheet2!emp_num_range),--(adm_date_cell<=Sheet2!adm_date_range),Sheet2!hours_range)
 
Back
Top