|
Problem with SUMIF
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. |
Problem with SUMIF
=SUMPRODUCT(--($G$11:$G$39="CR"),($Y$11:$Y$39)*($Z$11:$Z$39))/8
ought to get it. "SSG QuarterMaster" wrote: 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. |
Problem with SUMIF
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*S UMIF(G11:G39,"MIX",Z11:Z39))/8 -- Thanks, Shane Devenshire "SSG QuarterMaster" wrote: 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. |
Problem with SUMIF
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. "ShaneDevenshire" wrote: 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*S UMIF(G11:G39,"MIX",Z11:Z39))/8 -- Thanks, Shane Devenshire "SSG QuarterMaster" wrote: 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. |
| All times are GMT +1. The time now is 06:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com