Simple Formula (I thought)

C

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
 
B

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)
 
R

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
Click to expand...

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
 
Top