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

 

Resources