+ Reply to Thread
Results 1 to 4 of 4

Searching Document for Text, then adding together values of associated cells

  1. #1
    Registered User
    Join Date
    09-23-2018
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    2

    Searching Document for Text, then adding together values of associated cells

    Hi All,

    I'm not sure if this is possible to do with my current approach, so any direction is welcome. Please excuse any errors with my terminology also.

    The result I am aiming for is to have a sum total of hours for staff listed on a staff rota. Ideally I am trying to search across a field of various positions for initials indicating which staff are assigned which positions. I would then need to add the hours per shift, which are listed in an adjacent cell to find the total hours assigned per week.

    In the attached file is an example of one day. As you can see, staff can work multiple positions in a day (for example, "CF" is in two positions). Is there a way I can scan the document for "CF", and then somehow add the associated shift length (listed in the adjacent column, "C" in the example)
    so the document can automatically tell people that CF has 7 hours, LM has 7.5 Hours, etc? I'm not sure if a VLOOKUP would work for this scenario?

    Or if I am approaching this from a crazy angle (I'm aware I might be blinkered here!) could someone nudge me in a more appropriate direction?

    Thanks for reading.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Searching Document for Text, then adding together values of associated cells

    Welcome to the forum!

    Whilst aesthetically pleasing, your data is not in a sensible format for data analysis. Having said this, the formula below should do what you want if I have understood correctly:

    Excel 2016 (Windows) 32 bit
    E
    F
    3
    CF
    11
    Sheet: Sheet2

    Excel 2016 (Windows) 32 bit
    F
    3
    =SUMIF($A$3:$A$45,E3,$C$3:$C$45)
    Sheet: Sheet2

    Form should not come before function: collect data as individual records (rows) and then pull that data into the 'pretty' format you want to see it in. Avoid merged cells: they are the devil's spawn.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    09-23-2018
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Searching Document for Text, then adding together values of associated cells

    Thanks Ali, that works perfectly!

    The format I'm working with isn't ideal but stems from an existing document I'm trying to make more useful. Some of my colleagues would be adverse to more sensible layouts, so I am working with what I have!

    Thanks again, that's a great help.

    Phil

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Searching Document for Text, then adding together values of associated cells

    You're welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. 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. [SOLVED] Searching Column Multiple Times & Adding +1 To Text
    By bathtubs in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-05-2017, 12:36 AM
  2. [SOLVED] Searching for text and adding cells
    By MATT.B in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-18-2014, 09:32 AM
  3. Searching number in text field and adding value to it
    By virupaksh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2014, 10:40 AM
  4. Opening a word document and adding text to it
    By marcvanderpeet12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-30-2013, 07:16 AM
  5. Adding values of cells with similiar text
    By kuyleh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2012, 11:37 PM
  6. Problems searching Excel cells for InStr text comparison, cant get cell values ...
    By BaLLZaCH in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-27-2008, 05:52 AM
  7. Searching for text and adding a number value?
    By gsmonk in forum Excel General
    Replies: 2
    Last Post: 11-25-2007, 01:51 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