Sumproduct

[Ali Al Lawati]

i have created the following formulas any help so i can write the same in much shorter form 

=G23*SUMPRODUCT(Consumable!$B$3:$B$28,Consumable!$C$3:$C$28)+G24*SUMPRODUCT(Consumable!$B$3:$B$28,Consumable!$D$3:$D$28)+G25*SUMPRODUCT(Consumable!$B$3:$B$28,Consumable!$E$3:$E$28)+G26*SUMPRODUCT(Consumable!$B$3:$B$28,Consumable!$F$3:$F$28)+G27*SUMPRODUCT(Consumable!$B$3:$B$28,Consumable!$G$3:$G$28)+G28*SUMPRODUCT(Consumable!$B$3:$B$28,Consumable!$H$3:$H$28)+G29*SUMPRODUCT(Consumable!$B$3:$B$28,Consumable!$I$3:$I$28)+G30*SUMPRODUCT(Consumable!$B$3:$B$28,Consumable!$J$3:$J$28)+G31*SUMPRODUCT(Consumable!$B$3:$B$28,Consumable!$K$3:$K$28)+G32*SUMPRODUCT(Consumable!$B$3:$B$28,Consumable!$L$3:$L$28)

[#2]

Hi Ali,

Could be

=SUMPRODUCT(Consumable!$B$3:$B$28,MMULT(Consumable!$C$3:$L$28,G23:G32))

[#3]

Sorry did not work

[#4]

I tried to reproduce your case, no problems with formulas

and in attached file. If still doesn't work please attach sample file

[#5]

Thanks

it is working much appreciated 

i have few blank cells once i replaced with zero it worked perfectly 

[#6]

I see, thank you for the update

If blank cells are inside you may use

=SUMPRODUCT(Consumable!$B$3:$B$28,MMULT(IF(ISBLANK(Consumable!$C$3:$L$28),0,Consumable!$C$3:$L$28),G23:G32))

but that will be array formula (entered by Ctrl+Shift+Enter)

And attached.