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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> csandi
> ------------------------------------------------------------------------
> csandi's Profile:
http://www.excelforum.com/member.php...o&userid=28809
> View this thread: http://www.excelforum.com/showthread...hreadid=484973
>

[csandi]

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

[Ron Rosenfeld]

On Mon, 14 Nov 2005 13:45:27 -0600, csandi
<[email protected]> 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*{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