+ Reply to Thread
Results 1 to 12 of 12

Calculate currency values in a range of cells where the cells contain text and values

  1. #1
    Registered User
    Join Date
    09-15-2014
    Location
    West Sussex, England
    MS-Off Ver
    Office 365
    Posts
    30

    Calculate currency values in a range of cells where the cells contain text and values

    Hi there

    I need a formula to sum the values in a range of cells where the cells contain both text and values together. The values are a currency value.

    Below is a basic example.

    I understand I may also need to use VBA?

    Many thanks

    Monday: "Breakfast £100
    Lunch £50"
    Tuesday: "Breakfast £100
    Lunch £50"
    Wednesday: "Breakfast £100
    Lunch £50
    Dinner £150"



    TOTAL FORMULA?

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculate currency values in a range of cells where the cells contain text and values

    Why can't you arrange your date with Day/Breakfast/Lunch/Dinner as column headings??

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Calculate currency values in a range of cells where the cells contain text and values

    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and click "manage attachments" to open the upload window.


    To add a file to a post

  4. #4
    Registered User
    Join Date
    09-15-2014
    Location
    West Sussex, England
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Calculate currency values in a range of cells where the cells contain text and values

    This is just sample text and a simplified version of the actual. The actual is a calendar of events where there are values next to each as in the example where I need to calculate the values in a range of cells that contain text and values.

  5. #5
    Registered User
    Join Date
    09-15-2014
    Location
    West Sussex, England
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Calculate currency values in a range of cells where the cells contain text and values

    unfortunately my laptop securities prohibit me adding an attachments otherwise I would have.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculate currency values in a range of cells where the cells contain text and values

    Is this a single cell?

    Monday: "Breakfast £100 Lunch £50"

  7. #7
    Registered User
    Join Date
    09-15-2014
    Location
    West Sussex, England
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Calculate currency values in a range of cells where the cells contain text and values

    Yes and there are multiple similar entries in a range and I need to sum all the values in each cell across the range

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculate currency values in a range of cells where the cells contain text and values

    Please Login or Register  to view this content.


    e.g.

    =Sumvalues(A1:A5)

    Copy the Excel VBA code

    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
    [B][I]

  9. #9
    Registered User
    Join Date
    09-15-2014
    Location
    West Sussex, England
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Calculate currency values in a range of cells where the cells contain text and values

    formula.JPGformula.JPG

    Hi there

    Thank you so much but it doesn't seemed to have worked? The VB code says Sum_Values but the formula says =sumValues

  10. #10
    Registered User
    Join Date
    09-15-2014
    Location
    West Sussex, England
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Calculate currency values in a range of cells where the cells contain text and values

    Apologies I have tried again and it seems to have worked. Thank you very very much

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculate currency values in a range of cells where the cells contain text and values

    My typo!

    try this in B9

    =SUMPRODUCT(MID(B1:B7,FIND("£",B1:B7)+1,5)+0)

  12. #12
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Calculate currency values in a range of cells where the cells contain text and values

    If there would be blank cell the you will be in trouble again you ca use for below one.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

+ 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. [SOLVED] How to fill an Array with values from Range of cells, skip cells then add more values.
    By jrtraylor in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-04-2017, 06:27 PM
  2. counting cells in a range that contain any of several text values
    By nursedata in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2017, 06:24 PM
  3. Replies: 6
    Last Post: 09-16-2016, 03:52 PM
  4. Replies: 2
    Last Post: 09-07-2013, 07:50 PM
  5. [SOLVED] find unique values in a range that contains text and empty cells
    By bombicci in forum Excel General
    Replies: 4
    Last Post: 07-04-2012, 09:17 AM
  6. Replies: 1
    Last Post: 11-03-2011, 11:43 AM
  7. format cells as currency and insert formula values
    By fredpox in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-08-2010, 06:44 PM

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