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
Ali Al Lawati's avatar
Ali Al Lawati
Copper Contributor
Apr 28, 2018
Sorry did not work
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