multiply criteria in a range using sumifs
 

I hope someone can help, Please,
I'm using the following formula to get payroll data from 1 table into
another on a different sheet, the formula works great, however I need to add
to it and am having problems with calulating over time.

Table PP4_Hrs:
[Employee] [RT] [Income Type] [Hours] [Job] [Banked Pay Hrs]
payl 1 Over Time 1 429
hopm 0 Std. Income 1 429
janw 0 Std. Income 1 430
lowm 19 Banked Pay... 430 1
4 Total Hrs PP4
Table _429_ISIT:
[code] [PP4]
hopm 1
payl 1.5
2.5 Total Hrs Earned PP4

If the [RT] column =1 then the corrisponing [Hours] need to be multipled by
1.5 and added to the employee's total hrs in _429_ISIT Table. I tried
sumproduct but had no success, I don't think you can use array fomulas in a
table.??

This works:
SUMIFS(PP4_Hrs[Hours],PP4_Hrs[Job],429,PP4_Hrs[Employee],_429_ISIT[[#This
Row],[code]])+SUMIFS(PP4_Hrs[Pay Banked
Hrs],PP4_Hrs[Job],429,PP4_Hrs[Employee],_429_ISIT[[#This
Row],[code]],PP4_Hrs[Income Type],"Banked Pay...")

Adding this, doesn't:
+SUMifs(PP4_Hrs[Hours],PP4_Hrs[Job],429,PP4_Hrs[Employee],_429_ISIT[[#This
Row],[code]],PP4_Hrs[RT]=1,PP4_Hrs[Hours]*1.5)

I hope I've been clear, i've been working on this far too long.

Thanks in advance

Deb

multiply criteria in a range using sumifs
 

Solved it myself, thanks anyway.

"dloc" wrote:

I hope someone can help, Please,
I'm using the following formula to get payroll data from 1 table into
another on a different sheet, the formula works great, however I need to add
to it and am having problems with calulating over time.

Table PP4_Hrs:
[Employee] [RT] [Income Type] [Hours] [Job] [Banked Pay Hrs]
payl 1 Over Time 1 429
hopm 0 Std. Income 1 429
janw 0 Std. Income 1 430
lowm 19 Banked Pay... 430 1
4 Total Hrs PP4
Table _429_ISIT:
[code] [PP4]
hopm 1
payl 1.5
2.5 Total Hrs Earned PP4

If the [RT] column =1 then the corrisponing [Hours] need to be multipled by
1.5 and added to the employee's total hrs in _429_ISIT Table. I tried
sumproduct but had no success, I don't think you can use array fomulas in a
table.??

This works:
SUMIFS(PP4_Hrs[Hours],PP4_Hrs[Job],429,PP4_Hrs[Employee],_429_ISIT[[#This
Row],[code]])+SUMIFS(PP4_Hrs[Pay Banked
Hrs],PP4_Hrs[Job],429,PP4_Hrs[Employee],_429_ISIT[[#This
Row],[code]],PP4_Hrs[Income Type],"Banked Pay...")

Adding this, doesn't:
+SUMifs(PP4_Hrs[Hours],PP4_Hrs[Job],429,PP4_Hrs[Employee],_429_ISIT[[#This
Row],[code]],PP4_Hrs[RT]=1,PP4_Hrs[Hours]*1.5)

I hope I've been clear, i've been working on this far too long.

Thanks in advance

Deb