Find Cells in Relation to Other Cells With Certain Text

[Gator1130]

I am trying to subtract values in certain cells in this worksheet. I am trying to calculate profit by taking Total Retail and subtracting Total Cost. My problem is that the cell addresses for each total change on a daily basis depending on how many different item were sold that day. In the attached example, I need Excel to be able to find G21 and subtract E21 because those are the cells on the Dept Totals row.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J
Item Costing Report
From 02/07/17 05:00am To 02/08/17 04:59am
Item Number	Description	Qty Sold	Cost	Tot Cost	Retail	Tot Retl	% Cost	% Dpt Tot	% Tot Sls
Group: Beverage	Dept: Bottle Beer
BTL-BUD LIGHT	Bud Light
BTL-BUD LIGHT NFL CAN	Bud Light NFL Can
BTL-BUDWEISER	Budweiser
BTL-COORS LIGHT	Coors Light
BTL-DOS XX LAGER	Dos XX Lager
BTL-KEYSTONE LIGHT	Keystone Light
BTL-LONE STAR LIGHT	Lone Star Light
BTL-LONESTAR	Lone Star
BTL-MILLER LITE	Miller Lite
BTL-NATURAL LIGHT	Natural Light
BTL-SHINER BOCK	Shiner Bock
BTL-ZIEGENBOCK DRAFT	Ziegenbock Draft
Dept Totals
Group: Beverage	Dept: 1-Liquor
MIX-DRAGON ****	Dragon ****
TEQ-TRAIN WELL TEQ	Train Well Teq
TEQ-WELL TEQUILA	Well Tequila
VOD-WELL VODKA	Well Vodka

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]2/8/2017[/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"]3[/TD]
[TD="align: right"]12:41 AM[/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"]4[/TD]

[TD="align: center"]5[/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"]6[/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"]7[/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"]8[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]$0.86 [/TD]
[TD="align: right"]$25.80 [/TD]
[TD="align: right"]$2.75 [/TD]
[TD="align: right"]$72.00 [/TD]
[TD="align: right"]35.83[/TD]
[TD="align: right"]23.74[/TD]
[TD="align: right"]11.11[/TD]

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

[TD="align: right"]21[/TD]
[TD="align: right"]$0.70 [/TD]
[TD="align: right"]$14.70 [/TD]
[TD="align: right"]$1.75 [/TD]
[TD="align: right"]$36.75 [/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]12.12[/TD]
[TD="align: right"]5.67[/TD]

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

[TD="align: right"]2[/TD]
[TD="align: right"]$0.86 [/TD]
[TD="align: right"]$1.72 [/TD]
[TD="align: right"]$2.75 [/TD]
[TD="align: right"]$5.50 [/TD]
[TD="align: right"]31.27[/TD]
[TD="align: right"]1.81[/TD]
[TD="align: right"]0.85[/TD]

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

[TD="align: right"]16[/TD]
[TD="align: right"]$0.89 [/TD]
[TD="align: right"]$14.24 [/TD]
[TD="align: right"]$2.75 [/TD]
[TD="align: right"]$37.25 [/TD]
[TD="align: right"]38.23[/TD]
[TD="align: right"]12.28[/TD]
[TD="align: right"]5.75[/TD]

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

[TD="align: right"]3[/TD]
[TD="align: right"]$1.21 [/TD]
[TD="align: right"]$3.63 [/TD]
[TD="align: right"]$3.75 [/TD]
[TD="align: right"]$11.25 [/TD]
[TD="align: right"]32.27[/TD]
[TD="align: right"]3.71[/TD]
[TD="align: right"]1.74[/TD]

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

[TD="align: right"]5[/TD]
[TD="align: right"]$0.84 [/TD]
[TD="align: right"]$4.20 [/TD]
[TD="align: right"]$2.00 [/TD]
[TD="align: right"]$10.00 [/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]3.3[/TD]
[TD="align: right"]1.54[/TD]

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

[TD="align: right"]9[/TD]
[TD="align: right"]$0.86 [/TD]
[TD="align: right"]$7.74 [/TD]
[TD="align: right"]$2.75 [/TD]
[TD="align: right"]$24.75 [/TD]
[TD="align: right"]31.27[/TD]
[TD="align: right"]8.16[/TD]
[TD="align: right"]3.82[/TD]

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

[TD="align: right"]5[/TD]
[TD="align: right"]$0.86 [/TD]
[TD="align: right"]$4.30 [/TD]
[TD="align: right"]$2.75 [/TD]
[TD="align: right"]$13.75 [/TD]
[TD="align: right"]31.27[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]2.12[/TD]

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

[TD="align: right"]13[/TD]
[TD="align: right"]$0.89 [/TD]
[TD="align: right"]$11.57 [/TD]
[TD="align: right"]$2.75 [/TD]
[TD="align: right"]$32.00 [/TD]
[TD="align: right"]36.16[/TD]
[TD="align: right"]10.55[/TD]
[TD="align: right"]4.94[/TD]

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

[TD="align: right"]5[/TD]
[TD="align: right"]$0.70 [/TD]
[TD="align: right"]$3.50 [/TD]
[TD="align: right"]$2.25 [/TD]
[TD="align: right"]$11.25 [/TD]
[TD="align: right"]31.11[/TD]
[TD="align: right"]3.71[/TD]
[TD="align: right"]1.74[/TD]

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

[TD="align: right"]15[/TD]
[TD="align: right"]$1.13 [/TD]
[TD="align: right"]$16.95 [/TD]
[TD="align: right"]$3.75 [/TD]
[TD="align: right"]$37.50 [/TD]
[TD="align: right"]45.2[/TD]
[TD="align: right"]12.37[/TD]
[TD="align: right"]5.79[/TD]

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

[TD="align: right"]5[/TD]
[TD="align: right"]$0.58 [/TD]
[TD="align: right"]$2.90 [/TD]
[TD="align: right"]$2.25 [/TD]
[TD="align: right"]$11.25 [/TD]
[TD="align: right"]25.78[/TD]
[TD="align: right"]3.71[/TD]
[TD="align: right"]1.74[/TD]

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

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

[TD="align: right"]129[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$111.25 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]$303.25 [/TD]
[TD="align: right"]36.69[/TD]
[TD="align: right"][/TD]
[TD="align: right"]46.8[/TD]

[TD="align: center"]22[/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"]23[/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"]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: right"][/TD]

[TD="align: center"]25[/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"]26[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]$0.99 [/TD]
[TD="align: right"]$0.99 [/TD]
[TD="align: right"]$5.00 [/TD]
[TD="align: right"]$5.00 [/TD]
[TD="align: right"]19.8[/TD]
[TD="align: right"]3.51[/TD]
[TD="align: right"]0.77[/TD]

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

[TD="align: right"]2[/TD]
[TD="align: right"]$0.55 [/TD]
[TD="align: right"]$1.10 [/TD]
[TD="align: right"]$2.00 [/TD]
[TD="align: right"]$4.00 [/TD]
[TD="align: right"]27.5[/TD]
[TD="align: right"]2.81[/TD]
[TD="align: right"]0.62[/TD]

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

[TD="align: right"]1[/TD]
[TD="align: right"]$0.40 [/TD]
[TD="align: right"]$0.40 [/TD]
[TD="align: right"]$3.50 [/TD]
[TD="align: right"]$2.75 [/TD]
[TD="align: right"]14.55[/TD]
[TD="align: right"]1.93[/TD]
[TD="align: right"]0.42[/TD]

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

[TD="align: right"]5[/TD]
[TD="align: right"]$0.25 [/TD]
[TD="align: right"]$1.25 [/TD]
[TD="align: right"]$3.50 [/TD]
[TD="align: right"]$17.50 [/TD]
[TD="align: right"]7.14[/TD]
[TD="align: right"]12.3[/TD]
[TD="align: right"]2.7[/TD]

</tbody>

Item Costing Report Example​

 

[Gator1130]

Also, there will be other cells with the text "Dept Totals" There is one for each department. Ex. Bottle Beer, Liquor, Food, etc.

 

[Aladin Akyurek]

Let Sheet1 house the data.

Sheet2

[TABLE="class: grid, width: 352"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Bottle Beer[/TD]
[TD]16[/TD]
[TD]$192.00 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


In B2 of Sheet2 control+shift+enter, not just enter:

=MIN(IF(Sheet1!$B$6:$B$29="Dept Totals",IF(ROW(Sheet1!$B$6:$B$29)-ROW(Sheet1!$B$6)+1>MATCH("Dept: "&A2,Sheet1!$B$6:$B$29,0),ROW(Sheet1!$B$6:$B$29)-ROW(Sheet1!$B$6)+1)))

In C2 of Sheet2 just enter:

=INDEX(Sheet1!$B$6:$J$29,B2,MATCH("Tot Retl",Sheet1!$B$4:$J$4,0))-INDEX(Sheet1!$B$6:$J$29,B2,MATCH("Tot Cost",Sheet1!$B$4:$J$4,0))