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

**AlexanderDK** · 11-12-2019, 04:19 PM

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.

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

I hope you can help,

Thanks in advance!!

**loginjmor** · 11-12-2019, 05:05 PM

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.

**AlexanderDK** · 11-12-2019, 05:38 PM

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".

**loginjmor** · 11-12-2019, 06:13 PM

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,

**AlexanderDK** · 11-12-2019, 06:33 PM

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

**loginjmor** · 11-13-2019, 10:49 AM

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.

**Pepe Le Mokko** · 11-13-2019, 10:56 AM

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

**AlexanderDK** · 11-13-2019, 04:21 PM

Thanks loginjmor!!

It works!

**AlexanderDK** · 11-13-2019, 04:26 PM

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

**loginjmor** · 11-13-2019, 05:08 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Repeat Cell Values depending on Value in an Adjacent column

[SOLVED] Combo Box Values depending on adjacent cells

Adding Multiple Rows Depending on Column Content

[SOLVED] Inserting Values in a Cell Depending on Content in an Adjacent Cell

[SOLVED] Change a set of values with another set of values depending on “”Yes”/”No” in adjacent col

[SOLVED] How to autofill a column with a cell content depending on content in another cell.

Change ws3 Cell Content Depending On ws1 Content

Bookmarks

Bookmarks

Posting Permissions