Sum specific cells based on value in another cell.

[ascriver]

I would like to create a formula to automatically add the total hours per labeled time block based on the text above it. For example, I would like the formula to add the value in F26 based on the text in E24. to Does anyone know if this is possible, and if so what formula should I use? Please help!

 

[Eric W]

Welcome to the MrExcel forum.

For future reference, it's better to use one of the HTML tools (see my signature) to show your data. It's much easier for the helpers here to copy that onto their test spreadsheets than it is for them to actually have to retype everything. I normally wouldn't do it either, but I had some free time.

See if this works for you:


Excel 2012

	B	C	D	E	F	G	H	I	J	K	L
7-L	7-L	KAS	KAS
Time In
Time Out
KAS	KAS	KAS	7-L
Time In
Time Out
7-L	7-L	7-L	KAS
Time In
Time Out
KAS	KAS
Time In
Time Out
Total
KAS	7-L

<tbody>
[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]7:40 AM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]

[TD="align: center"]26[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]2:00[/TD]
[TD="align: right"]9:30 AM[/TD]
[TD="align: right"]1:50[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]4:00[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]9:30 AM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]

[TD="align: center"]29[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]2:00[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]2:30[/TD]
[TD="align: right"]3:30 PM[/TD]
[TD="align: right"]1:30[/TD]
[TD="align: right"]3:30 PM[/TD]
[TD="align: right"]2:30[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]3:30 PM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]3:30 PM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]

[TD="align: center"]32[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]1:30[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]1:30[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #8EA9DB"]Total[/TD]

[TD="align: center"]35[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]4:40 PM[/TD]
[TD="align: right"]2:40[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:00[/TD]

[TD="align: center"]36[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]22:10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9:50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D38[/TH]
[TD="align: left"]=SUMIF($E$24:$K$34,D37,$F$26:$L$36)[/TD]
[/TR]
[TR]
[TH]F38[/TH]
[TD="align: left"]=SUMIF($E$24:$K$34,F37,$F$26:$L$36)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




Also, note that merged cells can be really bad. Formulas often don't play nice with them. Using "Center across selection" is often better.