Looking up Categories on a different tab and summing the totals

[tandkb]

Hi,

I am tring to get Category totals looking up what category a meal is in from another tab and summing the totals on this page.

Category	Total $$
App
Salad
Entre
Drink

<tbody>
</tbody>

The table below is on a different tab

Dish	Category
Salsa & Chips	App
Bean Dip	App
Side Salad	Salad
Greek Salad	Salad
Italian Side	Salad
Steak	Entre
Hamburger	Entre
Taco	Entre
Coke	Drink
Tea	Drink
Lemonade	Drink
Coffee	Drink

<tbody>
</tbody>

The table below is on the same worksheet as the totals

Greek Salad	5
Coke	2
Hamburger	15
Steak	20
Bean Dip	6
Lemonade	2
Salsa & Chips	4
Taco	5
Coffee	2
Side Salad	5

<tbody>
</tbody>

I need the totals to look up what category each meal is in and give me the totals.

Thanks so much in advance!

 

[bbotzong]

Not terribly difficult if you can make one change... add the category to the table at the bottom, either to the left of the product (easiest to read), or to the right of the total sales (more difficult, but it can be done). You can do this one time using a VLOOKUP() function. Once your sales table looks like this:

Salad Greek Salad 5
Drink Coke 2
Entre Hamburger 15
Entre Steak 20
App Bean Dip 6
Drink Lemonade 2
App Salsa & Chips 4
Entre Taco 5
Drink Coffee 2
Salad Side Salad 5

you can then put in a SUMPRODUCT() formula on the other sheet to total them all up. Assuming the table above starts on row 18 and is on Sheet3, your formula on sheet4 where your categories start on row 3 would look like this:

=SUMPRODUCT((A3=Sheet3!$A$18:$A$27)*(Sheet3!$C$18:$C$27))

Copy this formula down for each category and you will get your correct totals.

 

[tandkb]

Thanks so much! I really don't want to add the category to the main sheet. I know it is difficult to do it the way I need but would love to figure out how to do it that way.

 

[DanteAmor]

Try this array formula

Totals Sheet
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:80.79px;" /><col style="width:76.04px;" /><col style="width:187.25px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Category</td><td style="background-color:#ffff00; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Total $$</td><td > </td><td style="background-color:#ffff00; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Dish</td><td style="background-color:#ffff00; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Total</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">App</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10</td><td > </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Greek Salad</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Salad</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10</td><td > </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Coke</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Entre</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">40</td><td > </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Hamburger</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">15</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Drink</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">6</td><td > </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Steak</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">20</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Bean Dip</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Lemonade</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Salsa & Chips</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Taco</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Coffee</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Side Salad</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">5</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >B2</td><td >{=SUM(IF(ISNUMBER(MATCH($D$2:$D$11,IF(Cat!$B$2:$B$13=A2,Cat!$A$2:$A$13),0)),$E$2:$E$11))}</td></tr></table></td></tr></table>


Cat Sheet
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:163.49px;" /><col style="width:163.49px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Dish</td><td style="background-color:#ffff00; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Category</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Salsa & Chips</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">App</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Bean Dip</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">App</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Side Salad</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Salad</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Greek Salad</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Salad</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Italian Side</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Salad</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Steak</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Entre</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Hamburger</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Entre</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Taco</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Entre</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Coke</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Drink</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Tea</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Drink</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Lemonade</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Drink</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Coffee</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Drink</td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.