Problem with SUMIF

[SSG QuarterMaster]

My problem arises from the below fomula.

=(SUMIF($G$11:$G$39,"CR",Z11:Z39)+0.5*SUMIF($G$11:$G$39,"MIX",Z11:Z39))/8

It works except that it is missing a critical piece. The data in cells
AA11:AA39 need to be multiplied by cell "Y".

This spreadsheet is used for OT requests. Our jobs are based on different
funding. This is goverened by column "$G$11:$G$39" with values of "CR", "MIX"
& "MF". I want cell "Z40" to sum up all numbers in column "Z" * by the
appropriate cell in column "Y".

G Y Z AA AB
10 CR-MIX-MF OT MON TUE WED
Hours
11 CR 2 1 2 2
12 CR 2 1 1 1
13 CR 4 5 5 5
......
40 3.0 3.250 3.250


Row 40 above is what the totals should be. This is what needs to happen:
1. Look in Column G for CR
2. Multiply column "Y" by column "Z"
3. Then, add up all the results

Something like this for all "CR" Columns: ((2*1)+(2*1)+(4*5))/8 = 3.0

Any help in this would be greatly appreciated.

 

[Sean Timmons]

=SUMPRODUCT(--($G$11:$G$39="CR"),($Y$11:$Y$39)*($Z$11:$Z$39))/8

ought to get it.

 

[ShaneDevenshire]

Hi,

I think you still need the SUMIF part:

=(SUMPRODUCT(($G$11:$G$39="CR")*Z11:Z39*Y11:Y39)+0.5*SUMIF($G$11:$G$39,"MIX",Z11:Z39))/8

If you aren't copying the formula:
=(SUMPRODUCT((G11:G39="CR")*Z11:Z39*Y11:Y39)+0.5*SUMIF(G11:G39,"MIX",Z11:Z39))/8

 

[SSG QuarterMaster]

Thanks a bunch on this one. I had spent about two days trying to figure this
out. I only had to duplicate it in the second half of the formula to the the
same thing for the "MIX" side which was 50/50 split.