[RESOLVED] Adding cells based on adjacent cell text

[kbrann]

I am looking for a function / custom function to automatically sum values from a column of cells based on the value of the cell next to it. I attached a copy of my spreadsheet. What I am trying to do is at the bottom of the spreadsheet is a tally of the total hours I spent through the week on each job. I want this to generate the total hours automatically. The job number cells change week to week and will be typed in manually.

So based on my example sheet attached, In Cell C57 I want to know the total number of hours I worked on the job shown in Cell B57. So the function should search for the job number in Column C, and add the total in column M wherever that job was recorded.
I was only able to attach a screenshot for some reason.

Thank you.

[vbfbryce]

Zip to attach the workbook

[kbrann]

Thanks for the tip. My example file is now attached.

[vbfbryce]

You can use the "sumif" function if you change your data slightly. If you put only the JOB# in cells b56 and b57, for example, you could use this formula in c56 (then copy to c57):

=SUMIF(C$4:C$10,B57,M$4:M$10)

[kbrann]

I thought about that originally but That is how accounting wants it formatted which is what brought me to asking for help here.

[vbfbryce]

ok, then maybe we can concatenate in the criteria of the sumif function instead. back with more on that shortly.

[vbfbryce]

Even that's going to be hard to do, unless you can ensure that the "syntax" in column D is the same as what's in column B. For example, if we concatenate C4 and D4 to get "36 In house" but in b57 we have "36 In-House" we won't get a match. Thoughts?

[kbrann]

The syntax may not be important if I just search based on the Job number. So the 36 becomes all that is important since up above the number and text are in separate columns.

[vbfbryce]

So you want to split the contents of cell b57, for example, so you only get the "36" part of it? Is it always going to be in the format Job# (space) Client?

[kbrann]

yes it will

[kbrann]

I figured out a solution. For a future reference if anyone has a similar issue, this is the formula I used.

=SUMIF($C$4:$M$54,LEFT(B57,FIND(" ",B57)-1),$M$4:$M$54)