Calculate pay budget for certain employees

[CMDohm]

I have a chart of employees - both regular and contract. I want to estimate my yearly budget for contractors.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Rate	Mar 2011	Apr 2011	May 2011	Jun 2011	Jul 2011	Aug 2011	Sep 2011	Oct 2011	Nov 2011	Dec 2011	Expected Result
2			184	168	168	176	160	184	168	168	168	168
3	Employee 1	$ 50.00	C	C	C	C	C	C	C	C	C		$ 77,200.00
4	Employee 2	$ 45.45		C	C	C	C	C	C	C	C	C	$ 69,447.60
5	Employee 3	$ -	F	F	F	F	F	F	F	F	F	F	$ -
6	Employee 4	$ -	F	F									$ -
7	Employee 5	$ -	F	F	F	F	F	F	F	F	F	F	$ -
8	Employee 6	$ -	F	F	F	F	F	F	F	F	F	F	$ -
2011Current

Excel 2007

Cell Formulas
Range	Formula
C2	=8*23
D2	=8*21
E2	=8*21
F2	=8*22
M3	=(C2*B3)+(D2*B3)+(E2*B3)+(F2*B3)+(G2*B3)+(H2*B3)+(I2*B3)+(J2*B3)+(K2*B3)
M4	=(D2*B4)+(E2*B4)+(F2*B4)+(G2*B4)+(H2*B4)+(I2*B4)+(J2*B4)+(K2*B4)+(L2*B4)

I want to calculate the total contract cost for each person based on the code, rather than by hand as is done here. If they were a contractor Jan-June and then hired, multiply the total hours in Jan-June by their rate, then sum the results.

Hope I explained it well....

 

[kpark91]

Would you like a VBA code for this?
and also, do they only work on the weekdays?

Lastly, what are C1 to L1 formatted as?

 

[Andrew Poulsom]

In M3 copied down:

=SUMIF(C4:L4,"C",C$3:L$3)*B4

 

[markmzz]

Or try this:

=SUMPRODUCT(--($C4:$L4="C"),$C$3:$L$3)*B4

Markmzz

 

[kpark91]

You can also automate column C to L with this formula
Put this in C3 and copy right and copy down.

=8 * NETWORKDAYS(C$1, EOMONTH(C$1,0))

However, this requires Analysis Tool Pack to be referenced and your headers in colC to L to be in date format.

 

[CMDohm]

Thanks! I tried the SUMIF function and it worked like a charm. I can see how the SUMPRODUCT would work as well.

I do this so infrequently these days...it's hard to remember the array stuff. The help is most appreciated!