#1
|
|||
|
|||
Give text a numeric value and multiply and add with different cells
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 |
#2
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
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 |