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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 30, 2019

jomynitima 

Let forget your formula for a while, what exactly you'd like to calculate for each Calc group listed in M4# spill?

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

    SergeiBaklan 

    First of all, a happy New Year!
    I am still puzzling, hope you can help

    In a table are specified in rows of materials, which at the end of this dynamic table are filled in in product columns with numbers.
    In another sheet the price of these products has to be calculated grouped for steel, wood, working time...
    I would like to use a validation to select products from the table headers, so that I can compare prices.

     

    Any help would be great.

     

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

      jomynitima 

      Happy New Year!

       

      Sorry, but I missed with your description. Could you please specify the same but more close to Excel file terms. 

      table are specified in rows of materials - is that Table16?

      end of this dynamic table - same table or another one?

      In another sheet the price.. - is that DT sheet?

      etc.

       

      I'm not familiar with your business and your project and don't remember what we discussed previous time, sorry for that. Please give more straightforward explanation what, where and base on what it shall be calculated.

       

      Thank you for understanding.

      • 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

         

  • jomynitima's avatar
    jomynitima
    Copper Contributor
    Dec 04, 2019

    Hello SergeiBaklan,

     

    What would be the correct formula to calculate 2 columns in a tabel, with criteria's.

     

    I am new here and would know how to find these kind of answers

     

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

    SergeiBaklan 

     

    In N9 I need to show how much fabric costs there is the product EARCHAIR (column Earchair, is written how much of the material (tabel Row, criteria CALC Group) is used in this product)

Resources