Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-07-2016, 09:20 AM
boeff boeff is offline Give text a numeric value and multiply and add with different cells Windows 7 64bit Give text a numeric value and multiply and add with different cells Office 2013
Novice
Give text a numeric value and multiply and add with different cells
 
Join Date: Jan 2016
Posts: 1
boeff is on a distinguished road
Question 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
Reply With Quote
  #2  
Old 01-07-2016, 09:54 AM
shg shg is offline Give text a numeric value and multiply and add with different cells Windows 7 64bit Give text a numeric value and multiply and add with different cells Office 2010 32bit
Advanced Beginner
 
Join Date: Oct 2015
Posts: 55
shg is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Give text a numeric value and multiply and add with different cells 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
Give text a numeric value and multiply and add with different cells Sum Formula in the range with Numeric and NonNumeric data cells Spanec Excel 2 01-12-2012 09:15 AM
Give text a numeric value and multiply and add with different cells How to give line numbering to only selected text? garlapati Word 5 04-04-2011 02:05 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft