+ Reply to Thread
Results 1 to 5 of 5

Sum cells along a row based on nearby cell values using a formula

  1. #1
    Registered User
    Join Date
    09-14-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Sum cells along a row based on nearby cell values using a formula

    Hi,

    I'd like to sum a number of cells along a row but only if the cell two from the cell I'd use in the calculation matches a given text.

    Example:

    In the attached spreadsheet I'd like to use a formula to:

    For Row 3:
    Add the cost of all Row 3 where mechanic BK worked on the car - result would go in cell V3 (currently manually inserted - 95)
    Add the cost of all Row 3 where mechanic HS worked on the car - result would go in cell W3 (currently manually inserted - 0)
    Add the cost of all Row 3 where mechanic JM worked on the car - result would go in cell X3 (currently manually inserted - 85)

    Thanks for your help.

    Sample File: sample sheet.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sum cells along a row based on nearby cell values using a formula

    Hi, welcome to the forum

    Couple of Questions
    - how set ar you on maintyaining the layout you have?
    - You have a heading of Vehicle, but under that you have car number (001, 006 etc)
    - are the values in A, dates, or.....
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sum cells along a row based on nearby cell values using a formula

    Hi mwhelan,

    This problem and your worksheet would be much better if you structured like the attached. Then you could sort, filter and even do Pivot Tables with your data. See the attached.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    09-14-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Sum cells along a row based on nearby cell values using a formula

    Hi, thanks for replies and suggestions so far...

    Unfortunately the layout is not flexible - this is a part of a much larger spreadsheet with additional columns and subtotal/total rows and it's set up this way by the user so not able to switch it around.

    The 'Vehicle 1' heading is just to show that it's the first car worked on during that shift, 'Vehicle 2' is the next car on the shift etc.. ( the car number - Car 0001 etc - are samples and in real life will be licence plate numbers)

    Column A will be shift number

    thanks

  5. #5
    Registered User
    Join Date
    09-14-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Sum cells along a row based on nearby cell values using a formula

    If I added in helper columns would this be of any benefit? thanks

+ 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: 1
    Last Post: 12-12-2012, 05:33 PM
  2. [SOLVED] Formula to write multiple values to a cell based on value of other cells
    By crazyjockey in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-05-2012, 09:32 PM
  3. Replies: 1
    Last Post: 05-29-2012, 01:04 PM
  4. Retrieve Data Using Find Function And Selecting Nearby Cells
    By csukenik in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-10-2011, 05:09 PM
  5. -how to use VBA to search strings and return values or cells nearby
    By dp256 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-20-2009, 04:24 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