Sum if two varialbles (sum if and if)
 

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 two varialbles (sum if and if)
 

=SUM(IF(EmployeeNumber=EmployeeNumber)*(Date=Admi ssionDate),NumberOfHours,))

array-enter it = CTRL+SHIFT+ENTER

Sum if two varialbles (sum if and if)
 

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!
--
John C


"Marco" wrote:

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 two varialbles (sum if and if)
 

=SUMPRODUCT(--(emp_num_cell=Sheet2!emp_num_range),--(adm_date_cell<=Sheet2!adm_date_range),Sheet2!hour s_range)

--
__________________________________
HTH

Bob

"Marco" wrote in message
...
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 two varialbles (sum if and if)
 

Hello,

It's not working. The results are all zero.

What can be wrong?

Thanks.




"John C" wrote:

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!
--
John C


"Marco" wrote:

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