Forum Discussion

jomynitima's avatar
jomynitima
Copper Contributor
Nov 30, 2019
Solved

combining sumifs and sumproduct

I need to calculate 2 columns in a table, with specific criteria, mentioned in a other column of the same table   =SUMIFS(SUMPRODUCT(Table4[Sales price per unit];Table4[Earchair]);"Table4[Calc gr...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 04, 2020

    jomynitima 

    I guess you are on DA Excel, thus have XLOOKUP(). Formula in D9 could be

    =SUMPRODUCT(IFERROR(XLOOKUP(D$3,Tabel16[#Headers],Tabel16[#Data])*1,0)*Tabel16[[Selling
in euro
Price]:[Selling
in euro
Price]]*(Tabel16[[Material
group]:[Material
group]]=$C9))*(1+$C$5+$C$6)

    and drag it down and to the right. I wrap first XLOOKUP with IFERROR since you have texts in some cells (actually spaces), such values are considered as zeroes.

jomynitima's avatar
jomynitima
Copper Contributor
Jan 04, 2020

SergeiBaklan 

apologies for my brief and incomplete explanation

Yes, in Table16 are all the material specs (Column A:L), starting from Column M, I am putting all amounts off materials that goes into the Products (Table-Headers).  These need to be calculated in DT!D9

I need to calculated SELLING PRICES * the amounts specified in MinMat!Column M

 

In TabDT D3:G3 is a criteria of the products that can choose to be calculated into the material groups sorted in C9 (also from Table16) starting in Cell D9

 

I hope this explains a little better what I need

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 04, 2020

jomynitima 

I guess you are on DA Excel, thus have XLOOKUP(). Formula in D9 could be

=SUMPRODUCT(IFERROR(XLOOKUP(D$3,Tabel16[#Headers],Tabel16[#Data])*1,0)*Tabel16[[Selling
in euro
Price]:[Selling
in euro
Price]]*(Tabel16[[Material
group]:[Material
group]]=$C9))*(1+$C$5+$C$6)

and drag it down and to the right. I wrap first XLOOKUP with IFERROR since you have texts in some cells (actually spaces), such values are considered as zeroes.

  • jomynitima's avatar
    jomynitima
    Copper Contributor
    Jan 04, 2020

    SergeiBaklan 

    Thanks so much, after I translated to English formulas to Dutch it works perfectly.  Now I just have to study it so I understand what I did wrong all this time.

     

    The only strange thing is that I cannot drag the formula. Probably an MS Insider (fast) bug they need to repair.  If the formula is in the formula-bar in 1 line it doesn't work and if it's in more lines it works!!? (screenshots).

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 05, 2020

      jomynitima 

      Without the file it's bit hard to say what are the issues. In general

      - you don't need to translate formulas manually. If you open the file attached to my previous post, formula automatically appears in your locale. In addition, you may use this great tool https://en.excel-translator.de/translator/

       

      - there is absolutely no difference in behavior of multi-line formula and one compacted into one line. Of course, if the latest done correctly. That includes ability to drag cell with formula.

Resources