Forum Discussion
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
group]";M9#)
It's should be dynamic too
Thanks for any help
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.
12 Replies
Let forget your formula for a while, what exactly you'd like to calculate for each Calc group listed in M4# spill?
First of all, a happy New Year!
I am still puzzling, hope you can helpIn 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.
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.
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
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)
