=ARRAYFORMULA(SUMPRODUCT((Products!B2:B=D2)*(IFS(H2="x",Products!D2:D,I2="x",Products!E2:E,J2="x",Products!F2:F)*(E2:E))))
I have a spreadsheet with an Orders sheet and a Products sheet. Product names (from B2:B) are data validated on the Orders sheet (D2:D) and also have adjacent cells with the quantity (E2:E) and 3 columns indicating what discount was given by use of an "x" (Consultant [H2:H], Preferred [I2:I], Retail [J2:J]). There are up to 3 prices per product (on Products sheet) reflective of the discount columns on the Orders sheet. I am trying to do the following:
Match ordered product (Orders!Product(D2:D)) with product name (Products!Product(B2:B)) multiply quantity (Orders!Qty(E2:E)) by price (Products!'Consultant Price'(D2:D), Products!'Preferred Price'(E2:E), Products!'Retail Price'(F2:F)) if discount (Orders!Consultant(H2:H), Orders!Preferred(I2:I), Orders!Retail(J2:J)) equals x
What functions or arrays should I be using differently.
Best Answer
was able to achieve this with a little assistance from a friend