Forum Discussion
combining sumifs and sumproduct
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.
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 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.
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.
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
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.