If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|