evaluate two cells and calculate based on criteria

[galiant]

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

[Don Guillett]

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
[email protected]
"galiant" <[email protected]> wrote in message
news:[email protected]...
>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

[galiant]

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
> [email protected]
> "galiant" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

[David Biddulph]

"galiant" <[email protected]> wrote in message
news:[email protected]...
> 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

[galiant]

Thanks, that was what I found in the help... ANy thoughts on the multiple
rows and generating a total?

"David Biddulph" wrote:

> "galiant" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[David Biddulph]

"galiant" <[email protected]> wrote in message
news:[email protected]...
....
> 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

[Don Guillett]

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,40)

--
Don Guillett
SalesAid Software
[email protected]
"galiant" <[email protected]> wrote in message
news:[email protected]...
>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