Forum Discussion

Ali Al Lawati's avatar
Ali Al Lawati
Copper Contributor
Apr 28, 2018

Sumproduct

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,Consuma...
Formulas & Functions
tips and tricks
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 28, 2018

I tried to reproduce your case, no problems with formulas

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

Ali Al Lawati's avatar
Ali Al Lawati
Copper Contributor
Apr 28, 2018

Thanks

it is working much appreciated 

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 28, 2018

    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.

Resources