I need to sum Programs (C6:F88) by color and by object class (B6:B88)
I can do the two separate functions: =SUMPRODUCT(--(LEFT($B$6:$B$88,3)="AAB"),C6:C88) and =sumbycolor(C15,C10:C88) but I can't figure out how I can do both. Pretend my example table had fill colors instead of text color.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Activity[/TD]
[TD]Object[/TD]
[TD]SC118[/TD]
[TD]SCE18[/TD]
[/TR]
[TR]
[TD]ASCS[/TD]
[TD]AAB[/TD]
[TD]10531.81[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ASCS[/TD]
[TD]AAC[/TD]
[TD]2974.99[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ASCS[/TD]
[TD]AAD[/TD]
[TD]3206.19[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ASCS[/TD]
[TD]AAE[/TD]
[TD]2343.93[/TD]
[TD]11.13[/TD]
[/TR]
[TR]
[TD]ASCS Total[/TD]
[TD][/TD]
[TD]19056.92[/TD]
[TD]11.13[/TD]
[/TR]
[TR]
[TD]CNA1[/TD]
[TD]AAB[/TD]
[TD]2895.2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]CNA1[/TD]
[TD]AAC[/TD]
[TD]833.47[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]CNA1[/TD]
[TD]AAD[/TD]
[TD]941.16[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]CNA1[/TD]
[TD]AAE[/TD]
[TD]1814.48[/TD]
[TD]181.83[/TD]
[/TR]
[TR]
[TD]CNA1 Total[/TD]
[TD][/TD]
[TD]6484.31[/TD]
[TD]181.83[/TD]
[/TR]
</tbody>[/TABLE]
I need the total of all the "red" "AAB" for the third and fourth columns. And then the total of all the "green" "AAB" and so on...
I can do the two separate functions: =SUMPRODUCT(--(LEFT($B$6:$B$88,3)="AAB"),C6:C88) and =sumbycolor(C15,C10:C88) but I can't figure out how I can do both. Pretend my example table had fill colors instead of text color.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Activity[/TD]
[TD]Object[/TD]
[TD]SC118[/TD]
[TD]SCE18[/TD]
[/TR]
[TR]
[TD]ASCS[/TD]
[TD]AAB[/TD]
[TD]10531.81[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ASCS[/TD]
[TD]AAC[/TD]
[TD]2974.99[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ASCS[/TD]
[TD]AAD[/TD]
[TD]3206.19[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ASCS[/TD]
[TD]AAE[/TD]
[TD]2343.93[/TD]
[TD]11.13[/TD]
[/TR]
[TR]
[TD]ASCS Total[/TD]
[TD][/TD]
[TD]19056.92[/TD]
[TD]11.13[/TD]
[/TR]
[TR]
[TD]CNA1[/TD]
[TD]AAB[/TD]
[TD]2895.2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]CNA1[/TD]
[TD]AAC[/TD]
[TD]833.47[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]CNA1[/TD]
[TD]AAD[/TD]
[TD]941.16[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]CNA1[/TD]
[TD]AAE[/TD]
[TD]1814.48[/TD]
[TD]181.83[/TD]
[/TR]
[TR]
[TD]CNA1 Total[/TD]
[TD][/TD]
[TD]6484.31[/TD]
[TD]181.83[/TD]
[/TR]
</tbody>[/TABLE]
I need the total of all the "red" "AAB" for the third and fourth columns. And then the total of all the "green" "AAB" and so on...