Give text a numeric value and multiply and add with different cells

boeff · #1 01-07-2016, 09:20 AM

Hi all,

i'm struggling with the last part of this formula and i hope someone can help out

Here my situation;

Column A, C, E are "QTY"
Column B, D, F are "Product" which could be either "burger", fries", "shake"
burger has a numeric value of 120, fries has a value of 110, and shake has a value of 140.

This is my formula;

=SUM(COUNTIF(B2:F2,{"burger","fries","shake"})*{12 0,110,140})* ......

I want either of these 3 values to be multiplied with the QTY in either Column A, C, E, eg; A2*B2, C2*D2, E2*F2, and then i want the total SUM displayed in Column G Row 2

Hope i was clear enough in explaining.

Best regards

shg · #2 01-07-2016, 09:54 AM

That's an awkward layout with interleaved columns.

Code:

     --A--- --B--- -C- --D-- -E- --F--- --G-- ---------------------------------------------H---------------------------------------------
 1    Item  Price
 2   Burger    120
 3   Fries     110
 4   Shake     140
 5
 6    Qty    Prod  Qty Prod  Qty  Prod  Total
 7        1 Burger   2 Fries   0 Shake    340 G7:=SUMPRODUCT(CHOOSE({1,2,3}, A7, C7, E7), LOOKUP(CHOOSE({1,2,3}, B7, D7, F7), $A$2:$B$4))
 8        3 Fries    1 Shake   5 Burger  1070

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
give mathematic functions numbers and link in the text	Meena2287	Word	3	09-11-2015 05:38 PM
how to give shadow or other effect to text	ketanco	Publisher	0	04-23-2015 05:19 AM
Copying specific cells to a new sheets also give sheetname	dapeamel	Excel	0	02-24-2015 09:20 AM
Sum Formula in the range with Numeric and NonNumeric data cells	Spanec	Excel	2	01-12-2012 09:15 AM
How to give line numbering to only selected text?	garlapati	Word	5	04-04-2011 02:05 PM