Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
evaluate two cells and calculate based on criteria
I need to create a function that allows me to evaluate two fields in multiple
rows and columns. So if cell A=Bob and cell B=DTY then multiply by 40 buty if cell A=Bob and cell B=VOL then multiply by 10. I am on a volunteer fire department and we pay several different ways for a run. So when I record a run I put a code next to the members name based on which way they are paid for that call. They could be on-call, volunteer, driver, etc and each is paid differently. I need to do this for several rows and columns. So my sheet might look like this and I need the formula to look at all the cells and make the evaluation and calculation and then add each total together. So I need to add all Bob's DTY pay with his VOL pay and get the total pay. Bob DTY Bo DTY Charles DRV Matt MMC Mike OS Don DTY Bob DTY Charles DRV Bo OS Don DTY Bob DTY Russ OS Russ DTY Bob VOL Charles DRV Mike OS Matt DTY Bob DTY Mike OS Russ DTY Bob DTY Charles DRV thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
evaluate two cells and calculate based on criteria
That seems to make sense, I am not sure the difference between the first and
second formula. in " =if(a2="bob",1,2)" what does the "1,2" mean? Also is there a way to have it evaluate numerous cells? So lets say you have 10 rows and each row has bob in one cell and either DTY or VOL. I see how the formula =if(a2="bob",if(b2="dty",40,if(b2="vol",10,0)),0) would calculate for a single row, but can it look at a range of rows and give me a total. So if bob was listed in all 10 rows it would look in each make the assesment of DTY or VOL and Calcualte wage for each row and then add all 10 rows together? "Don Guillett" wrote: how about something like this =if(a2="bob",1,2) =if(a2="bob",if(b2="dty",40,if(b2="vol",10,0)),0) -- Don Guillett SalesAid Software "galiant" wrote in message ... I need to create a function that allows me to evaluate two fields in multiple rows and columns. So if cell A=Bob and cell B=DTY then multiply by 40 buty if cell A=Bob and cell B=VOL then multiply by 10. I am on a volunteer fire department and we pay several different ways for a run. So when I record a run I put a code next to the members name based on which way they are paid for that call. They could be on-call, volunteer, driver, etc and each is paid differently. I need to do this for several rows and columns. So my sheet might look like this and I need the formula to look at all the cells and make the evaluation and calculation and then add each total together. So I need to add all Bob's DTY pay with his VOL pay and get the total pay. Bob DTY Bo DTY Charles DRV Matt MMC Mike OS Don DTY Bob DTY Charles DRV Bo OS Don DTY Bob DTY Russ OS Russ DTY Bob VOL Charles DRV Mike OS Matt DTY Bob DTY Mike OS Russ DTY Bob DTY Charles DRV thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
evaluate two cells and calculate based on criteria
"galiant" wrote in message
... That seems to make sense, I am not sure the difference between the first and second formula. in " =if(a2="bob",1,2)" what does the "1,2" mean? I suggest you look at Excel's help for the IF() function. If the condition is true then the answer is 1, whereas if the condition is false the answer is 2. -- David Biddulph |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
evaluate two cells and calculate based on criteria
Thanks, that was what I found in the help... ANy thoughts on the multiple
rows and generating a total? "David Biddulph" wrote: "galiant" wrote in message ... That seems to make sense, I am not sure the difference between the first and second formula. in " =if(a2="bob",1,2)" what does the "1,2" mean? I suggest you look at Excel's help for the IF() function. If the condition is true then the answer is 1, whereas if the condition is false the answer is 2. -- David Biddulph |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
evaluate two cells and calculate based on criteria
"galiant" wrote in message
... .... Also is there a way to have it evaluate numerous cells? So lets say you have 10 rows and each row has bob in one cell and either DTY or VOL. I see how the formula =if(a2="bob",if(b2="dty",40,if(b2="vol",10,0)),0) would calculate for a single row, but can it look at a range of rows and give me a total. So if bob was listed in all 10 rows it would look in each make the assesment of DTY or VOL and Calcualte wage for each row and then add all 10 rows together? Copy that formula down the column, and total the column. -- David Biddulph |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
evaluate two cells and calculate based on criteria
try this. this is an ARRAY formula that must be entered/edited using
ctrl+shift+enter vs just enter =SUM(IF(A2:A22="bob",C2:C22))*IF(B2:B22="vol",10,4 0) -- Don Guillett SalesAid Software "galiant" wrote in message ... I need to create a function that allows me to evaluate two fields in multiple rows and columns. So if cell A=Bob and cell B=DTY then multiply by 40 buty if cell A=Bob and cell B=VOL then multiply by 10. I am on a volunteer fire department and we pay several different ways for a run. So when I record a run I put a code next to the members name based on which way they are paid for that call. They could be on-call, volunteer, driver, etc and each is paid differently. I need to do this for several rows and columns. So my sheet might look like this and I need the formula to look at all the cells and make the evaluation and calculation and then add each total together. So I need to add all Bob's DTY pay with his VOL pay and get the total pay. Bob DTY Bo DTY Charles DRV Matt MMC Mike OS Don DTY Bob DTY Charles DRV Bo OS Don DTY Bob DTY Russ OS Russ DTY Bob VOL Charles DRV Mike OS Matt DTY Bob DTY Mike OS Russ DTY Bob DTY Charles DRV thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating an average based on 2 and 3 criteria | Excel Worksheet Functions | |||
calculate numbers based on font color? | Excel Worksheet Functions | |||
How do I only calculate cells which have a filled color format? | Excel Discussion (Misc queries) | |||
Subtract a group of cells from a total based on ending date | Excel Discussion (Misc queries) | |||
Calculate percentage based on cells with conditional formatting | New Users to Excel |