+ Reply to Thread
Results 1 to 6 of 6

SUM depending on Text formula

  1. #1
    Registered User
    Join Date
    10-08-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    7

    SUM depending on Text formula

    Hey guys,

    Thanks for the fantastic help last time. Hoping you can help me again!

    I want a formula that calculates a value by a certain amount, depending on what text is in the column next to it.

    Please find sample spreadsheet attached.

    So the values in column B in the DATA sheet, I want them multiplied by certain amounts, depending on what the text in column A is, and then fed over to the COSTING sheet.

    Low - 1
    Medium - 3
    High - 6
    Extreme - 10

    I hope that makes sense! Let me know if you need any further information.

    Cheers guys!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Kamboj's Avatar
    Join Date
    09-25-2014
    Location
    India
    MS-Off Ver
    2003 - 2010
    Posts
    430

    Re: SUM depending on Text formula

    can you please make one or two examples of your data to make more sense able.

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: SUM depending on Text formula

    You may try this...

    On Sheet COSTING
    In B2
    Please Login or Register  to view this content.
    and copy down.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: SUM depending on Text formula

    I added an extra column to your initial data and used VLOOKUP to assign the correct multipliers from a list. For the solution I used 2 VLOOKUP's that are multiplied - one pulls the number, the other pulls the multiplier.

    Since VLOOKUP is used, this will not work with multiple lookup values of the same kind, I'm sure you know this.
    Attached Files Attached Files

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: SUM depending on Text formula

    =SUM(IF(VLOOKUP(A2,DATA!$A$1:$C$7,2,0)={"Low","Medium","High","Extreme"},{1,3,6,10}))*VLOOKUP(A2,DATA!$A$1:$C$7,3,0)
    Try this in B2 and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: SUM depending on Text formula

    HI, Try this

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Boo?ath?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 21
    Last Post: 07-23-2014, 07:06 AM
  2. [SOLVED] Formula to Sum numbers depending on text in other cells
    By ExcelGal in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-11-2013, 10:30 AM
  3. Replies: 3
    Last Post: 12-17-2012, 07:28 AM
  4. Displaying text depending on result from other formula
    By likma in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 10-26-2011, 03:24 AM
  5. text colour change depending on outcome of formula
    By irons182 in forum Excel General
    Replies: 17
    Last Post: 07-07-2009, 10:33 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1