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

evaluate two cells and calculate based on criteria
 

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

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

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

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

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

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