Simple Formula (I thought)
 

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

Simple Formula (I thought)
 

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="M F",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" wrote in
message ...

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

Simple Formula (I thought)
 

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


--
csandi
------------------------------------------------------------------------
csandi's Profile: http://www.excelforum.com/member.php...o&userid=28809
View this thread: http://www.excelforum.com/showthread...hreadid=484973

Simple Formula (I thought)
 

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