Best formula to use for summing multiple column returns 1 row down from search value
I need to copy over data from one worksheet to a master worksheet. I do this with a new created worksheet every week. The new worksheets I create every week use the same cell layout so I would need to use a formula that I can copy and paste on the master worksheet.
The values I need to return and sum are 1 row below the lookup_value that I would normally use in Vlookup; they are also 8 columns across.
lookup_value is in cell A138, Values to return and sum are in cells H139:P139.
These are the same cells every worksheet.
I've tried to use variances of Sum(Vlookup($H$139:$P$139,{8,9,10,11,12,13,14,15,16} & Index(Match but continue to get #REF! or #Value!
what formula structure can I use to lookup_value and return 1 row below and sum columns H - P?
Image 1 of "Master Worksheet" formula is located in cell I1996 under "Qty in Transit" This row is in reference to Part # A03781402 shown on the right.
Image 2 of "Worksheet 1" You can see where Part # A03781402 is referenced in cell A138. Now I just need to grab the Qty from Cells H139:P139, sum & return to "Master Worksheet" Cell I1996
excel excel-formula vlookup sumifs
asked Dec 31 '18 at 23:06
Matt PiccaMatt Picca
909
add a comment |
I need to copy over data from one worksheet to a master worksheet. I do this with a new created worksheet every week. The new worksheets I create every week use the same cell layout so I would need to use a formula that I can copy and paste on the master worksheet.
The values I need to return and sum are 1 row below the lookup_value that I would normally use in Vlookup; they are also 8 columns across.
lookup_value is in cell A138, Values to return and sum are in cells H139:P139.
These are the same cells every worksheet.
I've tried to use variances of Sum(Vlookup($H$139:$P$139,{8,9,10,11,12,13,14,15,16} & Index(Match but continue to get #REF! or #Value!
what formula structure can I use to lookup_value and return 1 row below and sum columns H - P?
Image 1 of "Master Worksheet" formula is located in cell I1996 under "Qty in Transit" This row is in reference to Part # A03781402 shown on the right.
Image 2 of "Worksheet 1" You can see where Part # A03781402 is referenced in cell A138. Now I just need to grab the Qty from Cells H139:P139, sum & return to "Master Worksheet" Cell I1996
excel excel-formula vlookup sumifs
asked Dec 31 '18 at 23:06
Matt PiccaMatt Picca
909
The formula is deriving from "Master Worksheet". I am trying to lookup values from cells H139 - P139 based on Lookup_Value in cell A138 in "Worksheet 1"; sum the values and return the total value into another cell in "Master Worksheet"
– Matt Picca
Dec 31 '18 at 23:34
There isn't value in cell A138 its more of a reference to the cells below it. Maybe im just stuck in the vlookup logic.
– Matt Picca
Dec 31 '18 at 23:35
I will attached images to the main post, maybe that will help
– Matt Picca
Dec 31 '18 at 23:38
Where is the lookup value and in which range(column?) is it searched for? I know the rest.
– VBasic2008
Dec 31 '18 at 23:54
In "Master Worksheet" Lookup_value is in Column R (R1996). In Worksheet 1, Lookup_value is in column A (A138)
– Matt Picca
Jan 1 at 0:00
add a comment |
I need to copy over data from one worksheet to a master worksheet. I do this with a new created worksheet every week. The new worksheets I create every week use the same cell layout so I would need to use a formula that I can copy and paste on the master worksheet.
The values I need to return and sum are 1 row below the lookup_value that I would normally use in Vlookup; they are also 8 columns across.
lookup_value is in cell A138, Values to return and sum are in cells H139:P139.
These are the same cells every worksheet.
I've tried to use variances of Sum(Vlookup($H$139:$P$139,{8,9,10,11,12,13,14,15,16} & Index(Match but continue to get #REF! or #Value!
what formula structure can I use to lookup_value and return 1 row below and sum columns H - P?
Image 1 of "Master Worksheet" formula is located in cell I1996 under "Qty in Transit" This row is in reference to Part # A03781402 shown on the right.
Image 2 of "Worksheet 1" You can see where Part # A03781402 is referenced in cell A138. Now I just need to grab the Qty from Cells H139:P139, sum & return to "Master Worksheet" Cell I1996
excel excel-formula vlookup sumifs
asked Dec 31 '18 at 23:06
Matt PiccaMatt Picca
909
I need to copy over data from one worksheet to a master worksheet. I do this with a new created worksheet every week. The new worksheets I create every week use the same cell layout so I would need to use a formula that I can copy and paste on the master worksheet.
The values I need to return and sum are 1 row below the lookup_value that I would normally use in Vlookup; they are also 8 columns across.
lookup_value is in cell A138, Values to return and sum are in cells H139:P139.
These are the same cells every worksheet.
I've tried to use variances of Sum(Vlookup($H$139:$P$139,{8,9,10,11,12,13,14,15,16} & Index(Match but continue to get #REF! or #Value!
what formula structure can I use to lookup_value and return 1 row below and sum columns H - P?
Image 1 of "Master Worksheet" formula is located in cell I1996 under "Qty in Transit" This row is in reference to Part # A03781402 shown on the right.
Image 2 of "Worksheet 1" You can see where Part # A03781402 is referenced in cell A138. Now I just need to grab the Qty from Cells H139:P139, sum & return to "Master Worksheet" Cell I1996
excel excel-formula vlookup sumifs
excel excel-formula vlookup sumifs
asked Dec 31 '18 at 23:06
Matt PiccaMatt Picca
909
asked Dec 31 '18 at 23:06
Matt PiccaMatt Picca
909
edited Dec 31 '18 at 23:47
Matt Picca
asked Dec 31 '18 at 23:06
Matt PiccaMatt Picca
909
asked Dec 31 '18 at 23:06
Matt PiccaMatt Picca
909
asked Dec 31 '18 at 23:06
Matt PiccaMatt Picca
909
909
The formula is deriving from "Master Worksheet". I am trying to lookup values from cells H139 - P139 based on Lookup_Value in cell A138 in "Worksheet 1"; sum the values and return the total value into another cell in "Master Worksheet"
– Matt Picca
Dec 31 '18 at 23:34
There isn't value in cell A138 its more of a reference to the cells below it. Maybe im just stuck in the vlookup logic.
– Matt Picca
Dec 31 '18 at 23:35
I will attached images to the main post, maybe that will help
– Matt Picca
Dec 31 '18 at 23:38
Where is the lookup value and in which range(column?) is it searched for? I know the rest.
– VBasic2008
Dec 31 '18 at 23:54
In "Master Worksheet" Lookup_value is in Column R (R1996). In Worksheet 1, Lookup_value is in column A (A138)
– Matt Picca
Jan 1 at 0:00
add a comment |
The formula is deriving from "Master Worksheet". I am trying to lookup values from cells H139 - P139 based on Lookup_Value in cell A138 in "Worksheet 1"; sum the values and return the total value into another cell in "Master Worksheet"
– Matt Picca
Dec 31 '18 at 23:34
There isn't value in cell A138 its more of a reference to the cells below it. Maybe im just stuck in the vlookup logic.
– Matt Picca
Dec 31 '18 at 23:35
I will attached images to the main post, maybe that will help
– Matt Picca
Dec 31 '18 at 23:38
Where is the lookup value and in which range(column?) is it searched for? I know the rest.
– VBasic2008
Dec 31 '18 at 23:54
In "Master Worksheet" Lookup_value is in Column R (R1996). In Worksheet 1, Lookup_value is in column A (A138)
– Matt Picca
Jan 1 at 0:00
The formula is deriving from "Master Worksheet". I am trying to lookup values from cells H139 - P139 based on Lookup_Value in cell A138 in "Worksheet 1"; sum the values and return the total value into another cell in "Master Worksheet"
– Matt Picca
Dec 31 '18 at 23:34
The formula is deriving from "Master Worksheet". I am trying to lookup values from cells H139 - P139 based on Lookup_Value in cell A138 in "Worksheet 1"; sum the values and return the total value into another cell in "Master Worksheet"
– Matt Picca
Dec 31 '18 at 23:34
There isn't value in cell A138 its more of a reference to the cells below it. Maybe im just stuck in the vlookup logic.
– Matt Picca
Dec 31 '18 at 23:35
There isn't value in cell A138 its more of a reference to the cells below it. Maybe im just stuck in the vlookup logic.
– Matt Picca
Dec 31 '18 at 23:35
I will attached images to the main post, maybe that will help
– Matt Picca
Dec 31 '18 at 23:38
I will attached images to the main post, maybe that will help
– Matt Picca
Dec 31 '18 at 23:38
Where is the lookup value and in which range(column?) is it searched for? I know the rest.
– VBasic2008
Dec 31 '18 at 23:54
Where is the lookup value and in which range(column?) is it searched for? I know the rest.
– VBasic2008
Dec 31 '18 at 23:54
In "Master Worksheet" Lookup_value is in Column R (R1996). In Worksheet 1, Lookup_value is in column A (A138)
– Matt Picca
Jan 1 at 0:00
In "Master Worksheet" Lookup_value is in Column R (R1996). In Worksheet 1, Lookup_value is in column A (A138)
– Matt Picca
Jan 1 at 0:00
add a comment |
3 Answers
3
active
oldest
votes
Formula for 'Master Worksheet'!I1996 to sum 'Worksheet 1'!H:P one row below the row that matches 'Master Worksheet'!R1996 in 'Worksheet 1'!A:A.
=sum(index('Worksheet 1'!H:P, match(R1996, 'Worksheet 1'!A:A, 0)+1, 0))
The 0 as the column_num in INDEX is important; it means all columns within the indexed range.
answered Dec 31 '18 at 23:59
user10852207user10852207
2263
This worked! Thank you very much
– Matt Picca
Jan 4 at 22:54
add a comment |
You need to use something like a SUMIF:
=SUMIF($H$138:$P$138,$A$138,$H$139:$P$139)
answered Dec 31 '18 at 23:14
dwironydwirony
4,08631233
I just tried that formula, it's returning "0". But I have number values in cells O139 & P139. Maybe it due to the data coming from a different worksheet. Incorporating Vlookup with Sumif doesn't seem to work either.
– Matt Picca
Dec 31 '18 at 23:26
add a comment |
If the lookup_value is in Z1 and the lookup_array ColumnA, then please try:
=SUM(INDEX(H:P,1+MATCH(Z1,A:A,0),))
answered Dec 31 '18 at 23:42
pnutspnuts
48.8k76297
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53992044%2fbest-formula-to-use-for-summing-multiple-column-returns-1-row-down-from-search-v%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Formula for 'Master Worksheet'!I1996 to sum 'Worksheet 1'!H:P one row below the row that matches 'Master Worksheet'!R1996 in 'Worksheet 1'!A:A.
=sum(index('Worksheet 1'!H:P, match(R1996, 'Worksheet 1'!A:A, 0)+1, 0))
The 0 as the column_num in INDEX is important; it means all columns within the indexed range.
answered Dec 31 '18 at 23:59
user10852207user10852207
2263
This worked! Thank you very much
– Matt Picca
Jan 4 at 22:54
add a comment |
Formula for 'Master Worksheet'!I1996 to sum 'Worksheet 1'!H:P one row below the row that matches 'Master Worksheet'!R1996 in 'Worksheet 1'!A:A.
=sum(index('Worksheet 1'!H:P, match(R1996, 'Worksheet 1'!A:A, 0)+1, 0))
The 0 as the column_num in INDEX is important; it means all columns within the indexed range.
answered Dec 31 '18 at 23:59
user10852207user10852207
2263
This worked! Thank you very much
– Matt Picca
Jan 4 at 22:54
add a comment |
Formula for 'Master Worksheet'!I1996 to sum 'Worksheet 1'!H:P one row below the row that matches 'Master Worksheet'!R1996 in 'Worksheet 1'!A:A.
=sum(index('Worksheet 1'!H:P, match(R1996, 'Worksheet 1'!A:A, 0)+1, 0))
The 0 as the column_num in INDEX is important; it means all columns within the indexed range.
answered Dec 31 '18 at 23:59
user10852207user10852207
2263
Formula for 'Master Worksheet'!I1996 to sum 'Worksheet 1'!H:P one row below the row that matches 'Master Worksheet'!R1996 in 'Worksheet 1'!A:A.
=sum(index('Worksheet 1'!H:P, match(R1996, 'Worksheet 1'!A:A, 0)+1, 0))
The 0 as the column_num in INDEX is important; it means all columns within the indexed range.
answered Dec 31 '18 at 23:59
user10852207user10852207
2263
answered Dec 31 '18 at 23:59
user10852207user10852207
2263
answered Dec 31 '18 at 23:59
user10852207user10852207
2263
answered Dec 31 '18 at 23:59
user10852207user10852207
2263
2263
This worked! Thank you very much
– Matt Picca
Jan 4 at 22:54
add a comment |
This worked! Thank you very much
– Matt Picca
Jan 4 at 22:54
This worked! Thank you very much
– Matt Picca
Jan 4 at 22:54
This worked! Thank you very much
– Matt Picca
Jan 4 at 22:54
add a comment |
You need to use something like a SUMIF:
=SUMIF($H$138:$P$138,$A$138,$H$139:$P$139)
answered Dec 31 '18 at 23:14
dwironydwirony
4,08631233
I just tried that formula, it's returning "0". But I have number values in cells O139 & P139. Maybe it due to the data coming from a different worksheet. Incorporating Vlookup with Sumif doesn't seem to work either.
– Matt Picca
Dec 31 '18 at 23:26
add a comment |
You need to use something like a SUMIF:
=SUMIF($H$138:$P$138,$A$138,$H$139:$P$139)
answered Dec 31 '18 at 23:14
dwironydwirony
4,08631233
I just tried that formula, it's returning "0". But I have number values in cells O139 & P139. Maybe it due to the data coming from a different worksheet. Incorporating Vlookup with Sumif doesn't seem to work either.
– Matt Picca
Dec 31 '18 at 23:26
add a comment |
You need to use something like a SUMIF:
=SUMIF($H$138:$P$138,$A$138,$H$139:$P$139)
answered Dec 31 '18 at 23:14
dwironydwirony
4,08631233
You need to use something like a SUMIF:
=SUMIF($H$138:$P$138,$A$138,$H$139:$P$139)
answered Dec 31 '18 at 23:14
dwironydwirony
4,08631233
answered Dec 31 '18 at 23:14
dwironydwirony
4,08631233
answered Dec 31 '18 at 23:14
dwironydwirony
4,08631233
answered Dec 31 '18 at 23:14
dwironydwirony
4,08631233
4,08631233
I just tried that formula, it's returning "0". But I have number values in cells O139 & P139. Maybe it due to the data coming from a different worksheet. Incorporating Vlookup with Sumif doesn't seem to work either.
– Matt Picca
Dec 31 '18 at 23:26
add a comment |
I just tried that formula, it's returning "0". But I have number values in cells O139 & P139. Maybe it due to the data coming from a different worksheet. Incorporating Vlookup with Sumif doesn't seem to work either.
– Matt Picca
Dec 31 '18 at 23:26
I just tried that formula, it's returning "0". But I have number values in cells O139 & P139. Maybe it due to the data coming from a different worksheet. Incorporating Vlookup with Sumif doesn't seem to work either.
– Matt Picca
Dec 31 '18 at 23:26
I just tried that formula, it's returning "0". But I have number values in cells O139 & P139. Maybe it due to the data coming from a different worksheet. Incorporating Vlookup with Sumif doesn't seem to work either.
– Matt Picca
Dec 31 '18 at 23:26
add a comment |
If the lookup_value is in Z1 and the lookup_array ColumnA, then please try:
=SUM(INDEX(H:P,1+MATCH(Z1,A:A,0),))
answered Dec 31 '18 at 23:42
pnutspnuts
48.8k76297
add a comment |
If the lookup_value is in Z1 and the lookup_array ColumnA, then please try:
=SUM(INDEX(H:P,1+MATCH(Z1,A:A,0),))
answered Dec 31 '18 at 23:42
pnutspnuts
48.8k76297
add a comment |
If the lookup_value is in Z1 and the lookup_array ColumnA, then please try:
=SUM(INDEX(H:P,1+MATCH(Z1,A:A,0),))
answered Dec 31 '18 at 23:42
pnutspnuts
48.8k76297
If the lookup_value is in Z1 and the lookup_array ColumnA, then please try:
=SUM(INDEX(H:P,1+MATCH(Z1,A:A,0),))
answered Dec 31 '18 at 23:42
pnutspnuts
48.8k76297
answered Dec 31 '18 at 23:42
pnutspnuts
48.8k76297
answered Dec 31 '18 at 23:42
pnutspnuts
48.8k76297
answered Dec 31 '18 at 23:42
pnutspnuts
48.8k76297
48.8k76297
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53992044%2fbest-formula-to-use-for-summing-multiple-column-returns-1-row-down-from-search-v%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
The formula is deriving from "Master Worksheet". I am trying to lookup values from cells H139 - P139 based on Lookup_Value in cell A138 in "Worksheet 1"; sum the values and return the total value into another cell in "Master Worksheet"
– Matt Picca
Dec 31 '18 at 23:34
There isn't value in cell A138 its more of a reference to the cells below it. Maybe im just stuck in the vlookup logic.
– Matt Picca
Dec 31 '18 at 23:35
I will attached images to the main post, maybe that will help
– Matt Picca
Dec 31 '18 at 23:38
Where is the lookup value and in which range(column?) is it searched for? I know the rest.
– VBasic2008
Dec 31 '18 at 23:54
In "Master Worksheet" Lookup_value is in Column R (R1996). In Worksheet 1, Lookup_value is in column A (A138)
– Matt Picca
Jan 1 at 0:00