Using VLOOKUP for multiple lookup values, then summing the outputs
Example:
I have a sheet listing the number of calories for various foods.
In another sheet I have a list of some of those foods to be used in a meal, and would like to calculate the total number of calories for that particular meal.
How can I lookup the calorie data for each food item in the meal, and then sum each of those values together?
I thought something like =SUM(VLOOKUP(A2:A4,$Sheet1.A2:B13,2,0)) might work but alas not.
microsoft-excel worksheet-function libreoffice-calc spreadsheet vlookup
edited Jan 29 at 4:00
xypha
1,68111330
asked Jan 29 at 0:00
user991200user991200
111
add a comment |
Example:
I have a sheet listing the number of calories for various foods.
In another sheet I have a list of some of those foods to be used in a meal, and would like to calculate the total number of calories for that particular meal.
How can I lookup the calorie data for each food item in the meal, and then sum each of those values together?
I thought something like =SUM(VLOOKUP(A2:A4,$Sheet1.A2:B13,2,0)) might work but alas not.
microsoft-excel worksheet-function libreoffice-calc spreadsheet vlookup
edited Jan 29 at 4:00
xypha
1,68111330
asked Jan 29 at 0:00
user991200user991200
111
3
Do an individual VLOOKUP for each cell in the meal table, then use sum for the total.
– fixer1234
Jan 29 at 0:11
Try to use SUMPRODUCT function.
– Lee
Jan 29 at 8:56
Great tip while using excel. Never use vlookup, always use index-match. Much more reliable and versatile.
– Michthan
Jan 29 at 14:38
add a comment |
Example:
I have a sheet listing the number of calories for various foods.
In another sheet I have a list of some of those foods to be used in a meal, and would like to calculate the total number of calories for that particular meal.
How can I lookup the calorie data for each food item in the meal, and then sum each of those values together?
I thought something like =SUM(VLOOKUP(A2:A4,$Sheet1.A2:B13,2,0)) might work but alas not.
microsoft-excel worksheet-function libreoffice-calc spreadsheet vlookup
edited Jan 29 at 4:00
xypha
1,68111330
asked Jan 29 at 0:00
user991200user991200
111
Example:
I have a sheet listing the number of calories for various foods.
In another sheet I have a list of some of those foods to be used in a meal, and would like to calculate the total number of calories for that particular meal.
How can I lookup the calorie data for each food item in the meal, and then sum each of those values together?
I thought something like =SUM(VLOOKUP(A2:A4,$Sheet1.A2:B13,2,0)) might work but alas not.
microsoft-excel worksheet-function libreoffice-calc spreadsheet vlookup
microsoft-excel worksheet-function libreoffice-calc spreadsheet vlookup
edited Jan 29 at 4:00
xypha
1,68111330
asked Jan 29 at 0:00
user991200user991200
111
edited Jan 29 at 4:00
xypha
1,68111330
asked Jan 29 at 0:00
user991200user991200
111
edited Jan 29 at 4:00
xypha
1,68111330
edited Jan 29 at 4:00
xypha
1,68111330
edited Jan 29 at 4:00
xypha
1,68111330
1,68111330
asked Jan 29 at 0:00
user991200user991200
111
asked Jan 29 at 0:00
user991200user991200
111
asked Jan 29 at 0:00
user991200user991200
111
111
3
Do an individual VLOOKUP for each cell in the meal table, then use sum for the total.
– fixer1234
Jan 29 at 0:11
Try to use SUMPRODUCT function.
– Lee
Jan 29 at 8:56
Great tip while using excel. Never use vlookup, always use index-match. Much more reliable and versatile.
– Michthan
Jan 29 at 14:38
add a comment |
3
Do an individual VLOOKUP for each cell in the meal table, then use sum for the total.
– fixer1234
Jan 29 at 0:11
Try to use SUMPRODUCT function.
– Lee
Jan 29 at 8:56
Great tip while using excel. Never use vlookup, always use index-match. Much more reliable and versatile.
– Michthan
Jan 29 at 14:38
3
3
Do an individual VLOOKUP for each cell in the meal table, then use sum for the total.
– fixer1234
Jan 29 at 0:11
Do an individual VLOOKUP for each cell in the meal table, then use sum for the total.
– fixer1234
Jan 29 at 0:11
Try to use SUMPRODUCT function.
– Lee
Jan 29 at 8:56
Try to use SUMPRODUCT function.
– Lee
Jan 29 at 8:56
Great tip while using excel. Never use vlookup, always use index-match. Much more reliable and versatile.
– Michthan
Jan 29 at 14:38
Great tip while using excel. Never use vlookup, always use index-match. Much more reliable and versatile.
– Michthan
Jan 29 at 14:38
add a comment |
2 Answers
2
active
oldest
votes
Sum a SUMIFS:
=SUMPRODUCT(SUMIFS(Sheet1!B:B,Sheet1!A:A,A2:A5))
This will iterate the various parts of the meal in A2:A5 and sum the outputs of the SUMIFS.
answered Jan 29 at 14:22
Scott CranerScott Craner
12.4k11318
add a comment |
Use the B column to grab your calorie count with vlookup, then sum those at the bottom of the column.
B cells next to meal consumed:
=vlookup(A2,$Sheet1!A:B,2,0)
Then a simple sum of the column:
=sum(B2:B5)
Your function above may work as an array function by using cntrl-shift-enter to finalize it and changing your period to an exclamation mark. (untested)
answered Jan 29 at 0:16
BrianBrian
5487
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1399462%2fusing-vlookup-for-multiple-lookup-values-then-summing-the-outputs%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sum a SUMIFS:
=SUMPRODUCT(SUMIFS(Sheet1!B:B,Sheet1!A:A,A2:A5))
This will iterate the various parts of the meal in A2:A5 and sum the outputs of the SUMIFS.
answered Jan 29 at 14:22
Scott CranerScott Craner
12.4k11318
add a comment |
Sum a SUMIFS:
=SUMPRODUCT(SUMIFS(Sheet1!B:B,Sheet1!A:A,A2:A5))
This will iterate the various parts of the meal in A2:A5 and sum the outputs of the SUMIFS.
answered Jan 29 at 14:22
Scott CranerScott Craner
12.4k11318
add a comment |
Sum a SUMIFS:
=SUMPRODUCT(SUMIFS(Sheet1!B:B,Sheet1!A:A,A2:A5))
This will iterate the various parts of the meal in A2:A5 and sum the outputs of the SUMIFS.
answered Jan 29 at 14:22
Scott CranerScott Craner
12.4k11318
Sum a SUMIFS:
=SUMPRODUCT(SUMIFS(Sheet1!B:B,Sheet1!A:A,A2:A5))
This will iterate the various parts of the meal in A2:A5 and sum the outputs of the SUMIFS.
answered Jan 29 at 14:22
Scott CranerScott Craner
12.4k11318
answered Jan 29 at 14:22
Scott CranerScott Craner
12.4k11318
answered Jan 29 at 14:22
Scott CranerScott Craner
12.4k11318
answered Jan 29 at 14:22
Scott CranerScott Craner
12.4k11318
12.4k11318
add a comment |
add a comment |
Use the B column to grab your calorie count with vlookup, then sum those at the bottom of the column.
B cells next to meal consumed:
=vlookup(A2,$Sheet1!A:B,2,0)
Then a simple sum of the column:
=sum(B2:B5)
Your function above may work as an array function by using cntrl-shift-enter to finalize it and changing your period to an exclamation mark. (untested)
answered Jan 29 at 0:16
BrianBrian
5487
add a comment |
Use the B column to grab your calorie count with vlookup, then sum those at the bottom of the column.
B cells next to meal consumed:
=vlookup(A2,$Sheet1!A:B,2,0)
Then a simple sum of the column:
=sum(B2:B5)
Your function above may work as an array function by using cntrl-shift-enter to finalize it and changing your period to an exclamation mark. (untested)
answered Jan 29 at 0:16
BrianBrian
5487
add a comment |
Use the B column to grab your calorie count with vlookup, then sum those at the bottom of the column.
B cells next to meal consumed:
=vlookup(A2,$Sheet1!A:B,2,0)
Then a simple sum of the column:
=sum(B2:B5)
Your function above may work as an array function by using cntrl-shift-enter to finalize it and changing your period to an exclamation mark. (untested)
answered Jan 29 at 0:16
BrianBrian
5487
Use the B column to grab your calorie count with vlookup, then sum those at the bottom of the column.
B cells next to meal consumed:
=vlookup(A2,$Sheet1!A:B,2,0)
Then a simple sum of the column:
=sum(B2:B5)
Your function above may work as an array function by using cntrl-shift-enter to finalize it and changing your period to an exclamation mark. (untested)
answered Jan 29 at 0:16
BrianBrian
5487
answered Jan 29 at 0:16
BrianBrian
5487
answered Jan 29 at 0:16
BrianBrian
5487
answered Jan 29 at 0:16
BrianBrian
5487
5487
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1399462%2fusing-vlookup-for-multiple-lookup-values-then-summing-the-outputs%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Do an individual VLOOKUP for each cell in the meal table, then use sum for the total.
– fixer1234
Jan 29 at 0:11
Try to use SUMPRODUCT function.
– Lee
Jan 29 at 8:56
Great tip while using excel. Never use vlookup, always use index-match. Much more reliable and versatile.
– Michthan
Jan 29 at 14:38