+ Reply to Thread
Results 1 to 3 of 3

Find a text and return the sum of values in adjacent cells matching the text in range

  1. #1
    Registered User
    Join Date
    08-25-2013
    Location
    california
    MS-Off Ver
    Excel 2010
    Posts
    10

    Find a text and return the sum of values in adjacent cells matching the text in range

    I have a contractors that provide services under Normal categories and Special categories.
    The log is kept for their services. Anytime a contractor works, an entry is made into the log for hours worked and type of work.

    I have values as shown below in columns A to J (as a sample). The actual data extends a couple hundred columns.

    A B C D E F G H I J
    1 N 0.5 N 0.5 S 2 S 2 N


    It shows that the contractor provided "Normal" services 3 times and "Special" services 2 times.
    I am looking for a formula that will calculate the SUM of hours spent under each category.

    E.g for Normal(N) , the formula should return 3.5
    i.e find the instances of N and add the value in the cell to the left of it.

    NO VBA pls.
    Any way to do it by just using functions??

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,756

    Re: Find a text and return the sum of values in adjacent cells matching the text in range

    Try this:

    =SUMIF(B1:J1,"N",A1:I1)

    Note that the two ranges are the same size, but offset by one column - adjust the ranges to suit your data.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-25-2013
    Location
    california
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Find a text and return the sum of values in adjacent cells matching the text in range

    Thanks Pete.

    This is very helpful. I had a few more columns in between that I had not mentioned in the question. But I adjusted the formula and it solved the need.

+ 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: 6
    Last Post: 03-06-2013, 03:30 PM
  2. [SOLVED] Help writing a macro to find text and copy values to adjacent cells
    By Nnnnnnnick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2012, 10:07 AM
  3. [SOLVED] Find text in a column or range and return value of adjacent cell
    By chemoul in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2012, 04:30 AM
  4. [SOLVED] Find Specific Text in Text String and Return Value in Adjacent Column
    By watchouse in forum Excel General
    Replies: 2
    Last Post: 07-11-2012, 03:53 PM
  5. Traverse a colum match a text and return sum of all values in adjacent cells
    By realdost in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2012, 09:49 PM

Tags for this Thread

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