Sep 25 2022 07:40 PM
Sep 25 2022 07:40 PM
Hi please can someone help with a formula I am trying to create:
I have multiple products in one column which I want to identify and then multiply those products that match say 'Adventurer', multiply that by $2.20 and then by the quantity in another column to get a total. Or if it is say 'Workplace', multiply that by $2.40, then by the quantity to get a total.
Quantity Product name Total 1 [Consignment fee] 4.00 1 Adventurer Plus Rescue Swag 2.20 1 Workplace Rescue Swag 2.40 8.60 1 [Consignment fee] 4.00 1 Adventurer Plus Rescue Swag 2.40 6.40 1 [Consignment fee] 4.00 1 Adventurer Rescue Swag 2.20 1 Dry Bag 10L 0.50 6.70 1 [Consignment fee] 1 Adventurer Rescue Swag + Motorcycle Kit Bundle 1 CAT Tourniquet 1 Snake Bite Indicator Bandage
Sep 25 2022 09:04 PM
@diidunlop Perhaps the attached file will help you on your way, hoping that I interpreted your question correctly. Created a lookup table with Product and Unit Price (Col H:I). Then I used VLOOKUP in column C to find the price for the product in column B. Column D contains the formula for multiplying Qty and Unit Price. If your Excel version supports dynamic array function you could replace VLOOKUP by XLOOKUP. See column F.
Sep 26 2022 09:50 AM
@diidunlop I agree with @Riny_van_Eekelen 's use of a table to store the prices. However, I'd also want to suggest some redesign of the entire sheet. Normally, one wants to keep a consistent formula all the way down in a column, but your initial design (at least as shown in your original post) does not do that. As a result, using the formulas as done in Riny's spreadsheet--although the formula you requested is exactly what I'd do--because the formula that calculates the totals is included in to the same column, this produces a warning flag as shown in the following screen grab:
In order to best resolve this, though, we need to know what your ultimate use is here. That is, what's the bigger picture into which this fits? Are you wanting to know how to price orders for customers? [That appears to be it.] If so, it could be better to, in effect, create a single invoice sheet--one at a time, not all squeezed in together on one sheet, which would handle the circumstances of some ordering one item, others two or more......The kind of "design" you have now will almost inevitably lead to confusion, if not to errors.
So could I request that you back up and explain the context here?
Sep 26 2022 10:34 AM
I've attached a sample of what I was suggesting, building on the solution that @Riny_van_Eekelen created (and actually just copying his formula), but now creating a separate sheet that will generate an invoice for up to 9 items per order.
The table of products can be expanded, and the data validation will track with it, as will the calculations. One of the advantages of using a table like Riny gave you: you can change prices on any item on the spot, without revising the formula.