On Mon, 14 Nov 2005 13:45:27 -0600, csandi
wrote:


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*{2 0,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*TRANSP OSE(Rates)))

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


--ron