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
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