Simple Formula (I thought)

[csandi]

Been racking my brains all day on this, I'm sure one of you clever
people out there can help me. I need to do a simple calculation based
on hourly rates, example as follows.
TA bills out at $20 an hour
MF bills out at $50 an hour
FG bills out at $70 an hour

All the above are in the same column of a worksheet....how the hell do
I get a total for the above as a grand total. i.e. IF A1 = TA, do a
calculation, add to total. IF A6 = MF do a calculation add to total and
so on. The initials are in drop down boxes....and the input needs to be
simple cos numptys like me have to do this...

Any help would be very appreciated.
Chris
AKA Excel virgin

 

[Bob Phillips]

Presumably, you want to multiply by the hours, in column B?

If so, use

=SUMIF(A1:A100,H1,B1:B100)*(IF(H1="TA",20,IF(H1="MF",50,IF(H1="FG",70,0))))

where H1 holds the id to calculate.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[csandi]

Your a genious...definately on my Christmas Card list.
Thanks
Chri

 

[Ron Rosenfeld]

Been racking my brains all day on this, I'm sure one of you clever
people out there can help me. I need to do a simple calculation based
on hourly rates, example as follows.
TA bills out at $20 an hour
MF bills out at $50 an hour
FG bills out at $70 an hour

All the above are in the same column of a worksheet....how the hell do
I get a total for the above as a grand total. i.e. IF A1 = TA, do a
calculation, add to total. IF A6 = MF do a calculation add to total and
so on. The initials are in drop down boxes....and the input needs to be
simple cos numptys like me have to do this...

Any help would be very appreciated.
Chris
AKA Excel virgin

A single formula for the Grand Total would be:

=SUMPRODUCT((A1:A100={"TA","MF","FG"})*(B1:B100*{20,50,70}))

However, if your list of initials is in some column named "initials" and your
associated list of rates is in some column named "rates", then the following
**array** formula might be more flexible for future editing.

To enter an **array** formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.


=SUM((A1:A100=TRANSPOSE(Initials))*(B1:B100*TRANSPOSE(Rates)))

(If Initials and Rates are in rows instead of columns, you won't need the
Transpose Function)


--ron