I have a table (below) that I want to add a total at the bottom of the column D to represent the total of RED and the total of BLUE based on the following formula.
For red: sum of ((Rate 1 + Rate 2)*hours)*Red Factor
For blue: sum of ((Rate 1 + Rate 2)*hours)*Blue Factor
I think it would be a sumproduct and a sumif-type formula possibly. i.e. sumif columns C & D then multiply the result by Column B then that total by the Factor.
Red Factor 2.02
Blue Factor 1.50
Column A Column B Column C Column D (Example of the math for each row that I would like to total at the bottom of column D based on blue or red)
Hours Rate 1 Rate 2
RED 25.00 $8.00 $2.23 ((8.00+2.23)*25)*2.02
BLUE 21.00 $7.23 $1.74 ((7.23+1.74)*21)*1.50
BLUE 19.00 $6.15 $2.54 ((6.15+2.54)*19)*1.50
BLUE 17.00 $9.54 $1.65 ((9.54+1.65)*17)*1.50
RED 22.00 $7.56 $1.42 ((7.56+1.42)*22)*2.02
RED 23.00 $8.41 $2.17 ((8.41+2.17)*23)*2.02
The results would be as follows:
RED: $1,407.23
BLUE: $815.57
Note that I cannot add another column.
Thanks!
Chad
For red: sum of ((Rate 1 + Rate 2)*hours)*Red Factor
For blue: sum of ((Rate 1 + Rate 2)*hours)*Blue Factor
I think it would be a sumproduct and a sumif-type formula possibly. i.e. sumif columns C & D then multiply the result by Column B then that total by the Factor.
Red Factor 2.02
Blue Factor 1.50
Column A Column B Column C Column D (Example of the math for each row that I would like to total at the bottom of column D based on blue or red)
Hours Rate 1 Rate 2
RED 25.00 $8.00 $2.23 ((8.00+2.23)*25)*2.02
BLUE 21.00 $7.23 $1.74 ((7.23+1.74)*21)*1.50
BLUE 19.00 $6.15 $2.54 ((6.15+2.54)*19)*1.50
BLUE 17.00 $9.54 $1.65 ((9.54+1.65)*17)*1.50
RED 22.00 $7.56 $1.42 ((7.56+1.42)*22)*2.02
RED 23.00 $8.41 $2.17 ((8.41+2.17)*23)*2.02
The results would be as follows:
RED: $1,407.23
BLUE: $815.57
Note that I cannot add another column.
Thanks!
Chad
