Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplifying a couple of formulas
I have created a sheet for my workplace that is used for tracking types of
pay (ie: sick, manager, supervisor,cashier, vacation or holiday) or any combination of three of those. I have my sheet setup with names, days of the week, types of pay, totals etc. Each Day is made up of four cells, shift(d4), type of hours(e4), # hrs(f4), 2nd # hrs(g4) arranged left to right. The typr of hours is a dropdown list (just validation) and all the arguments are firgured out by the other three numbers. I have been using several IF formulas combined to add the days of the week up (Extremely condensed example) =sum(if(or(e4="reg",e4="reg\vac",e4="reg\sck"),f4, 0),if(or(i4="reg",i4="reg\vac",i4="reg\sck"),f4,0) ,.... Basically it's the sum of 7 IF formula's with multiple arguments for each '4 cell' day. How can I use one IF formula with all my arguments and apply that to each '4 cell' day with out typing the same formula out seven times. Thanks in advance for any help offered. fryguy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplifying a couple of formulas
=IF(SUMPRODUCT(COUNTIF(INDIRECT({"E4","I4","L4","K 4"}),{"reg";"reg\vac";"reg\sck"}))0,F4,0)
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "fryguy" wrote in message ... I have created a sheet for my workplace that is used for tracking types of pay (ie: sick, manager, supervisor,cashier, vacation or holiday) or any combination of three of those. I have my sheet setup with names, days of the week, types of pay, totals etc. Each Day is made up of four cells, shift(d4), type of hours(e4), # hrs(f4), 2nd # hrs(g4) arranged left to right. The typr of hours is a dropdown list (just validation) and all the arguments are firgured out by the other three numbers. I have been using several IF formulas combined to add the days of the week up (Extremely condensed example) =sum(if(or(e4="reg",e4="reg\vac",e4="reg\sck"),f4, 0),if(or(i4="reg",i4="reg\vac",i4="reg\sck"),f4,0) ,.... Basically it's the sum of 7 IF formula's with multiple arguments for each '4 cell' day. How can I use one IF formula with all my arguments and apply that to each '4 cell' day with out typing the same formula out seven times. Thanks in advance for any help offered. fryguy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplifying a couple of formulas
My mistake. As stated your equation worked but my should have read
=sum(if(or(e4="reg",e4="reg\vac",e4="reg\sck"),f4, 0),if(or(i4="reg",i4="reg\vac",i4="reg\sck"),j4,0) ,.... The difference being the second Value(f4) should have been J4, the value of the #hours worked on that day (friday) Please Help fryguy. "Bob Phillips" wrote: =IF(SUMPRODUCT(COUNTIF(INDIRECT({"E4","I4","L4","K 4"}),{"reg";"reg\vac";"reg\sck"}))0,F4,0) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "fryguy" wrote in message ... I have created a sheet for my workplace that is used for tracking types of pay (ie: sick, manager, supervisor,cashier, vacation or holiday) or any combination of three of those. I have my sheet setup with names, days of the week, types of pay, totals etc. Each Day is made up of four cells, shift(d4), type of hours(e4), # hrs(f4), 2nd # hrs(g4) arranged left to right. The typr of hours is a dropdown list (just validation) and all the arguments are firgured out by the other three numbers. I have been using several IF formulas combined to add the days of the week up (Extremely condensed example) =sum(if(or(e4="reg",e4="reg\vac",e4="reg\sck"),f4, 0),if(or(i4="reg",i4="reg\vac",i4="reg\sck"),f4,0) ,.... Basically it's the sum of 7 IF formula's with multiple arguments for each '4 cell' day. How can I use one IF formula with all my arguments and apply that to each '4 cell' day with out typing the same formula out seven times. Thanks in advance for any help offered. fryguy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying Cell Formulas | Excel Discussion (Misc queries) | |||
How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
how can i get formulas in excel to copy and paste? | Excel Worksheet Functions | |||
How prevent formulas to get external references/path to current workbook? | Excel Worksheet Functions | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions |