Creating total from list

K

kmc

Board Regular
Joined
Sep 5, 2005
Messages
158
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I have two sheets involved and am trying to create a total from the summary sheet into the buying list sheet.
The use is for a camp food list. I actually have 9 columns of items and there are some duplicates that need to be added up. I also have about 40 rows on the summary sheet.
I used =SUMIF for the summary sheet as there is another sheet with the specifics for each meal and day.


Excel 2010
ABCDEFG
1DayMainSideSalad/Fruit
21bacon (ea)20beans (oz)50Apple (ea)25
32Bagels (ea)20chips (ea)20Banana (ea)20
43Baked ham (oz)20Muffins (ea)35orange (ea)23
54Beef (oz)100Spread (T)16raisans (box20
65Bread (ea)25Stove top Stuffing (oz)60peaches (ea)35
76Bread Italian (ea)25Green Beans (Fzn oz)25croutons (oz)75
87Buns (ea)20Canned potato (ea)4Fruit ****tail (oz)50
Summary


So my question is what formula do I use below to sum the items in the sheet above. As mentioned above, I have other columns and I need to do other calcs once the totals are available. The sheet is not final yet, as I know I could convert to values and just add up. I'm wanting the formulas to remain active.


Excel 2010
ABC
1ItemMealTotal
2Apple (ea)salad/FruitNeed formula
3bacon (ea)main
4Bagels (ea)main
5Baked ham (oz)main
6Banana (ea)salad/Fruit
7beans (oz)side
8Beef (oz)main
9Bread (ea)main
10Bread Italian (ea)main
11Buns (ea)main
12Canned potato (ea)side
13chips (ea)side
14croutons (oz)salad/Fruit
15Fruit ****tail (oz)salad/Fruit
16Green Beans (Fzn oz)side
17Muffins (ea)side
18orange (ea)salad/Fruit
19peaches (ea)salad/Fruit
20raisans (box)salad/Fruit
21Spread (T)side
22Stove top Stuffing (oz)side
Buying list
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
This?


Excel 2010
ABCDEFGHIJKL
1DayMainSideSalad/FruitItemMealTotal
21bacon (ea)20beans (oz)50Apple (ea)25Apple (ea)salad/Fruit25
32Bagels (ea)20chips (ea)20Banana (ea)20bacon (ea)main20
43Baked ham (oz)20Muffins (ea)35orange (ea)23Bagels (ea)main20
54Beef (oz)100Spread (T)16raisans (box)20Baked ham (oz)main20
65Bread (ea)25Stove top Stuffing (oz)60peaches (ea)35Banana (ea)salad/Fruit20
76Bread Italian (ea)25Green Beans (Fzn oz)25croutons (oz)75beans (oz)side50
87Buns (ea)20Canned potato (ea)4Fruit ****tail (oz)50Beef (oz)main100
9Bread (ea)main25
10Bread Italian (ea)main25
11Buns (ea)main20
12Canned potato (ea)side4
13chips (ea)side20
14croutons (oz)salad/Fruit75
15Fruit ****tail (oz)salad/Fruit50
16Green Beans (Fzn oz)side25
17Muffins (ea)side35
18orange (ea)salad/Fruit23
19peaches (ea)salad/Fruit35
20raisans (box)salad/Fruit20
21Spread (T)side16
22Stove top Stuffing (oz)side60
Sheet10 (2)
Cell Formulas
RangeFormula
L2=SUMPRODUCT(--($B$2:$F$8=J2),OFFSET($B$2:$F$8,0,1))
 
Last edited:
Upvote 0
In C2 of Buying list enter and copy down:
Rich (BB code): 
=VLOOKUP(A2,CHOOSE({1,2},INDEX(Summary!$B$2:$F$8,0,MATCH(B2,Summary!$B$1:$F$1,0)),
    INDEX(Summary!$B$2:$G$8,0,MATCH(B2,Summary!$B$1:$F$1,0)+1)),2,0)
 
Upvote 0

Unknown
ABCDEFG
1DayMainSideSalad/Fruit
21bacon (ea)20beans (oz)50Apple (ea)25
32Bagels (ea)20chips (ea)20Banana (ea)20
43Baked ham (oz)20Muffins (ea)35orange (ea)23
54Beef (oz)100Spread (T)16raisans (box)20
65Bread (ea)25Stove top Stuffing (oz)60peaches (ea)35
76Bread Italian (ea)25Green Beans (Fzn oz)25croutons (oz)75
87Buns (ea)20Canned potato (ea)4Fruit ****tail (oz)50
9
10
Summary




Unknown
ABC
1ItemMealTotal
2Apple (ea)salad/Fruit25
3bacon (ea)main20
4Bagels (ea)main20
5Baked ham (oz)main20
6Banana (ea)salad/Fruit20
7beans (oz)side50
8Beef (oz)main100
9Bread (ea)main25
10Bread Italian (ea)main25
11Buns (ea)main20
12Canned potato (ea)side4
13chips (ea)side20
14croutons (oz)salad/Fruit75
15Fruit ****tail (oz)salad/Fruit50
16Green Beans (Fzn oz)side25
17Muffins (ea)side35
18orange (ea)salad/Fruit23
19peaches (ea)salad/Fruit35
20raisans (box)salad/Fruit20
21Spread (T)side16
22Stove top Stuffing (oz)side60
23
Buying List
Cell Formulas
RangeFormula
C2=VLOOKUP(A2,INDEX(Summary!$B$2:$G$8,1,MATCH(B2,Summary!$B$1:$G$1,0)):INDEX(Summary!$B$2:$G$8,ROWS(Summary!$C$2:$C$8),MATCH(B2,Summary!$B$1:$G$1,0)+1),2,0)
 
Upvote 0

Unknown
ABCDEFG
1DayMainSideSalad/Fruit
21bacon (ea)20beans (oz)50Apple (ea)25
32Bagels (ea)20chips (ea)20Banana (ea)20
43Baked ham (oz)20Muffins (ea)35orange (ea)23
54Beef (oz)100Spread (T)16raisans (box)20
65Bread (ea)25Stove top Stuffing (oz)60peaches (ea)35
76Bread Italian (ea)25Green Beans (Fzn oz)25croutons (oz)75
87Buns (ea)20Canned potato (ea)4Fruit ****tail (oz)50
Summary



Unknown
ABC
1ItemMealTotal
2Apple (ea)salad/Fruit25
3bacon (ea)main20
4Bagels (ea)main20
5Baked ham (oz)main20
6Banana (ea)salad/Fruit20
7beans (oz)side50
8Beef (oz)main100
9Bread (ea)main25
10Bread Italian (ea)main25
11Buns (ea)main20
12Canned potato (ea)side4
13chips (ea)side20
14croutons (oz)salad/Fruit75
15Fruit ****tail (oz)salad/Fruit50
16Green Beans (Fzn oz)side25
17Muffins (ea)side35
18orange (ea)salad/Fruit23
19peaches (ea)salad/Fruit35
20raisans (box)salad/Fruit20
21Spread (T)side16
22Stove top Stuffing (oz)side60
Buying List
Cell Formulas
RangeFormula
C2=VLOOKUP(A2,INDEX(Summary!$B$2:$G$8,1,MATCH(B2,Summary!$B$1:$G$1,0)):Summary!$G$8,2,0)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
List generated from item selection
Replies
9
Views
540
ExceLoki
ExceLoki
JeffGrant
  • Question Question
Splitting from Outlook Email Body
2
Replies
12
Views
910
Kerryx
K
C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
665
fjns
F
M
  • Question Question
Create new workbook for every sheet in active workbook, starting from Sheet(3) and saving them to a user specified folder location.
Replies
0
Views
979
Mars84
M
B
  • Solved
Create Unique List from array with criteria
Replies
9
Views
395
Fluff
Fluff

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top