VLOOKUP?

matthewparry45

New Member
Joined
Jul 21, 2002
Messages
18
This is my third attempt to resolve a problem.

I have two tabs. The text in column B of Tab 1 lists a variety of foods, under two different headings ("Grains" and "Proteins"). Each item (e.g., "wheat", "corn", "meat") has related numerical data in columns C, D and E.
I have defined the "Grains" range as B2:E5, and "Proteins" as B9:E11, so they include the name of each item and the related numerical values.

In tab 2, I want to sum the totals of those items in colum E on Tab 1 that are grains separately from those that are proteins.

For example, In Tab 1:

A B C D E

1
2 GRAINS Fat Cals Water
3 Wheat 10 15 20
4 Corn 30 35 40
5 Rice 45 50 60
6
7
8 PROTEINS Fat Cals Water
9 Meat 5 6 7
10 Chicken 8 9 10
11 Lentils 11 12 13


Tab 2 looks like this

A B C D E

2 COMMODITY Fat Cals Water
3 Wheat 10 15 20
4 Lentils 11 12 13
5 Chicken 8 9 10
6 Rice 45 50 60
7 Meat 5 6 7
8 Corn 30 35 40


What I want to do is insert a formula in Tab 2 that sorts the individual "Grains" items from the "Proteins" items, then adds their respective totals. The results I want from Column E (Water) are this:

Grains 120 (i.e., 20+40+60)
Proteins 20 (i.e., 7+10+13)

I don't know which function (SUMIF, VLOOKUP, combination, other?).

Can anyone help?

Thanks,
MatthewParry45
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
It's not clear to me whether you already have a table in "Tab 2" and if so, what you want to do with it.

To sum 'water' from the 4-column Grains table, use:

=SUM(INDEX(Grains,0,4))

To sum 'water' from the 4-column Proteins table, use:

=SUM(INDEX(Proteins,0,4))
 

Forum statistics

Threads
1,144,363
Messages
5,723,919
Members
422,527
Latest member
JayTheKaz

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
Top