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.
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.
Thanks so much, after I translated to English formulas to Dutch it works perfectly. Now I just have to study it so I understand what I did wrong all this time.
The only strange thing is that I cannot drag the formula. Probably an MS Insider (fast) bug they need to repair. If the formula is in the formula-bar in 1 line it doesn't work and if it's in more lines it works!!? (screenshots).
Thank you. I'll check bit later tonight what I can do, explanation looks understandable (for me of course) now.
