vlookup summary

[bill9324]

In the department expense table below, I would like to determine a rate (c1) by adding the value in col c for 60700 and 60715 and dividing that by the col c values for 60100 thru 60300. I figured this out by using a long version of vlookup but am sure something simplier exists. Can't use (c10+c11)/sum(c4:c9) because the col A variables change position department by department.

<TABLE style="WIDTH: 284pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=378><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 7021" width=192><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 height=20 width=64>

​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 width=64>

A​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 144pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 width=192>

B​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 width=58>

C​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>

Benefits Rate​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>60100</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> Wages - Exempt</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>170,698 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>60101</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> Regular Pay - Non-Exempt</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>2,075 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>60195</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> Wages Alloc</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>270,356 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>7</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>60225</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> Overtime Alloc</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>3,435 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>8</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>60295</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> Bonus Alloc</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>6,130 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>9</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>60300</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> PTO Expense</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>1,146 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>60700</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> Benefits</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>38,440 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>11</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>60715</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> Benefits Alloc</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>60,556 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>60750</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> Relocation</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>89,370 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>13</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>61010</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> Travel Exp</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>19,575 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>14</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>61020</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> Reimbursed Gas & Mileage</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>378 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>15</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>61030</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63> Meals & Ent</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 align=right>3,297 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 align=right>61040</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65> Empl. Activities</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl66 align=right>(584)</TD></TR></TBODY></TABLE>

 

[Robert Mika]

Try this:
=SUMIF($B$4:$B$16,"*Benefit*",$C$4:$C$16)

This formula will summarize all cells in column C where coresponding cell B includes word "Benefit"

 

[bill9324]

Thank you Robert. How about to sum the other items in col A (60100 - 60300). I need the denominator so I can calc a %? Didn't work nesting OR in the search value criteria.

 

[Robert Mika]

What kind of criteria you would like to use for this (60100 - 60300) range?
or is (60100 - 60300) so you want to return sum of this range from column C?

 

[bill9324]

Sum similar to the first solution you provided but specifically for the col c values for 60100 thru 60300. I need to specifically select them because the row position of 60100 thru 60300 will vary.

 

[Robert Mika]

Try this array formula:
Confirm CTRL+SHIFT+ENTER:
=SUM(INDEX(C1:C30,MATCH(60100,A1:A30,0)):INDEX(C1:C30,MATCH(60300,A1:A30,0)))

 

[bill9324]

I overlooked thanking you for your help with this, everything worked perfectly!

Please excuse my oversight and thank you again.