Forum Discussion
combining sumifs and sumproduct
- Jan 04, 2020
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.
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.
- jomynitimaJan 04, 2020Copper Contributor
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).
- SergeiBaklanJan 05, 2020Diamond Contributor
Without the file it's bit hard to say what are the issues. In general
- you don't need to translate formulas manually. If you open the file attached to my previous post, formula automatically appears in your locale. In addition, you may use this great tool https://en.excel-translator.de/translator/
- there is absolutely no difference in behavior of multi-line formula and one compacted into one line. Of course, if the latest done correctly. That includes ability to drag cell with formula.
- jomynitimaJan 05, 2020Copper Contributor
Thanks again Sergei
I added the file that I typed in the translated formula, for you to have a look at.
Hope you find something wrong in it.
Have a nice weekend