Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIFS - How to use a variable with or < in criteria | Excel Worksheet Functions | |||
SUMIFS with an OR criteria | Excel Worksheet Functions | |||
using sumifs with multiple criteria? | Excel Discussion (Misc queries) | |||
sumifs criteria | Excel Worksheet Functions | |||
SUMIFS with 3 criteria instead of just 2 | Excel Worksheet Functions |