+ Reply to Thread
Results 1 to 10 of 10

How to sum multiple values depending on the content of the adjacent cell??

  1. #1
    Registered User
    Join Date
    11-12-2019
    Location
    Belgium
    MS-Off Ver
    Office 365
    Posts
    21

    Question How to sum multiple values depending on the content of the adjacent cell??

    Hi guys,

    I have been looking all day long for a single formula (may be a big one) to sum multiple values witch depend on the adjacent cell but i'm not making a lot of progress. I hope you can help me.

    Like you can see in the file, there is a complete array of data to the right and i have to make some calculations on the left.
    Now, i'm looking for a formula witch will sum all the numbers to the right adjacent cell of "OT". But there are a few tricky things.

    1. The layout can NOT be changed.
    2. "OT" will only be written in the first column of the day.
    3. But "OT" can be written in any of the rows witch are available for each person (e.g. 3 rows for Person 1).
    4. "OT" can appear multiple times every week.


    I hope you can help,

    Thanks in advance!!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: How to sum multiple values depending on the content of the adjacent cell??

    Hi -

    I think if you place this ARRAY formula in cell F6, press ctrl-shift-enter to engage the array functionality (Excel will place curly brackets {} around the formula if you did it right) it should give you the sum of the cells immediately to the right of the cell that contains "OT".

    The formula looks like:

    =SUM(IF(IFERROR(SEARCH("OT",M6:BR6),0),N6:BS6,0))

    Hope that helps.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Registered User
    Join Date
    11-12-2019
    Location
    Belgium
    MS-Off Ver
    Office 365
    Posts
    21

    Re: How to sum multiple values depending on the content of the adjacent cell??

    Thanks for the answer longinjmor. Unfortunately it didn't help.

    If i enlarge the search area over the 3 rows which are available for person 1, the formula won't work anymore.

    The result needs to return -7 in cell G6 instead of -16.
    If you look more to the right you will see cells with exclusively "OT" and not "OT 18". Once the formula works for "OT", i can hopefully make it work as well for "OT 18".

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: How to sum multiple values depending on the content of the adjacent cell??

    Hi -

    I must not understand how you are classifying OT. The formula will work for "OT" and for "OT 18". Any text string that has the letters "OT" in it (Including "OT 18") are counted.

    So, help me understand. In the first row, there is an OT 18 in Cell M6. Immediately to the right of that is -8. The next instance of OT 18 in that row is in cell T6, and immediately to the right of that is -8. There are no other OT or OT 18 in that row. So the total for that row is -16.

    If we go to the next row, in Cell AA7 there is "OT". Immediately to the right of that is -2. The next instance of "OT" in that row is AQ7 and to the right of that is -3. The total for that row is -5.

    Following the same logic, for Row 8, the total is -2. So, for Person 1, the total overtime is -16 + -5 + -2 which is -23.

    I have attached your spreadsheet with this formula in the total overtime cells F6, F7, and F8. What am I doing wrong?

    Thanks,
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-12-2019
    Location
    Belgium
    MS-Off Ver
    Office 365
    Posts
    21

    Re: How to sum multiple values depending on the content of the adjacent cell??

    Hi

    Now i see how you look at my problem! My bad!

    First:
    "OT" is used for the year 2019 only. If necessary, it may be changed to "OT19" if that makes it easier.
    "OT 18" is used for 2018 only. The space may be removed. It isn't important for anything else.

    Second:
    I would like to have the total result for each year per person in one cell. So 2018 in C6 and 2019 in F6 for Person 1.


    Thanks,

    Alex

  6. #6
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: How to sum multiple values depending on the content of the adjacent cell??

    Hi -

    OK, I have modified the cells that said "OT" and changed them to "OT 19". I also edited the formula to sum all 3 rows for Person 1. Since there are 4 rows for Person 2, I had to manually change the range to add the extra row. Person 3 is back to 3 rows so I could simply copy and paste from C6 and F6 to C 13 and F13. The array formula in C6 looks like:

    =SUM(IF(IFERROR(SEARCH("OT 18",M6:BR8),0),N6:BS8,0))

    And the array formula in F6 looks like:

    =SUM(IF(IFERROR(SEARCH("OT 19",M6:BR8),0),N6:BS8,0))

    Hope this helps.
    Attached Files Attached Files

  7. #7
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,741

    Re: How to sum multiple values depending on the content of the adjacent cell??

    Lots of merged cells... To be avoided at all costs

  8. #8
    Registered User
    Join Date
    11-12-2019
    Location
    Belgium
    MS-Off Ver
    Office 365
    Posts
    21

    Re: How to sum multiple values depending on the content of the adjacent cell??

    Thanks loginjmor!!

    It works!

  9. #9
    Registered User
    Join Date
    11-12-2019
    Location
    Belgium
    MS-Off Ver
    Office 365
    Posts
    21

    Re: How to sum multiple values depending on the content of the adjacent cell??

    Pepe Le Mokko, i know. Unfortunately it's not my own creation. And some people may not like a complete new design of a spreadsheet

  10. #10
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: How to sum multiple values depending on the content of the adjacent cell??

    Great! Glad we could help. Don't forget to mark your thread as SOLVED per the instructions at the bottom of my first post.

+ 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] Repeat Cell Values depending on Value in an Adjacent column
    By 125ml in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-13-2018, 08:49 AM
  2. [SOLVED] Combo Box Values depending on adjacent cells
    By Umair.711 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-03-2014, 02:51 AM
  3. Adding Multiple Rows Depending on Column Content
    By ryanb4614 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2014, 09:58 AM
  4. [SOLVED] Inserting Values in a Cell Depending on Content in an Adjacent Cell
    By lowlybroker in forum Excel General
    Replies: 3
    Last Post: 03-20-2014, 01:23 PM
  5. Replies: 8
    Last Post: 08-25-2012, 04:27 AM
  6. [SOLVED] How to autofill a column with a cell content depending on content in another cell.
    By lul1971 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-17-2012, 12:33 PM
  7. Change ws3 Cell Content Depending On ws1 Content
    By heliskier89 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2011, 04:52 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