Find the sum of cell array, only if the row contains a specific value
I'm trying to find a way to get the sum of an array, but only if a cell in the same row(s) contains a specific value. For example, I would like to sum all the values in cells E7:AZ14, if the value in cells C7:C14 = Apple
The way I am currently approaching this - without success - is as follows (an array formula that combines the SUM and VLOOKUP functions):
{=SUM(VLOOKUP("Apple",C7:AZ14,{3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52},0))*C:AZ}
Any help would be greatly appreciated!
arrays excel excel-formula sum vlookup
asked Nov 23 '18 at 18:36
Steve BSteve B
103
add a comment |
I'm trying to find a way to get the sum of an array, but only if a cell in the same row(s) contains a specific value. For example, I would like to sum all the values in cells E7:AZ14, if the value in cells C7:C14 = Apple
The way I am currently approaching this - without success - is as follows (an array formula that combines the SUM and VLOOKUP functions):
{=SUM(VLOOKUP("Apple",C7:AZ14,{3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52},0))*C:AZ}
Any help would be greatly appreciated!
arrays excel excel-formula sum vlookup
asked Nov 23 '18 at 18:36
Steve BSteve B
103
add a comment |
I'm trying to find a way to get the sum of an array, but only if a cell in the same row(s) contains a specific value. For example, I would like to sum all the values in cells E7:AZ14, if the value in cells C7:C14 = Apple
The way I am currently approaching this - without success - is as follows (an array formula that combines the SUM and VLOOKUP functions):
{=SUM(VLOOKUP("Apple",C7:AZ14,{3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52},0))*C:AZ}
Any help would be greatly appreciated!
arrays excel excel-formula sum vlookup
asked Nov 23 '18 at 18:36
Steve BSteve B
103
I'm trying to find a way to get the sum of an array, but only if a cell in the same row(s) contains a specific value. For example, I would like to sum all the values in cells E7:AZ14, if the value in cells C7:C14 = Apple
The way I am currently approaching this - without success - is as follows (an array formula that combines the SUM and VLOOKUP functions):
{=SUM(VLOOKUP("Apple",C7:AZ14,{3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52},0))*C:AZ}
Any help would be greatly appreciated!
arrays excel excel-formula sum vlookup
arrays excel excel-formula sum vlookup
asked Nov 23 '18 at 18:36
Steve BSteve B
103
asked Nov 23 '18 at 18:36
Steve BSteve B
103
asked Nov 23 '18 at 18:36
Steve BSteve B
103
asked Nov 23 '18 at 18:36
Steve BSteve B
103
asked Nov 23 '18 at 18:36
Steve BSteve B
103
103
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Maybe
=SUMPRODUCT((C7:C14="Apple")*(E7:AZ14))
answered Nov 23 '18 at 19:18
QHarrQHarr
32.1k82042
@ForwardEd You are absolutely correct.
– QHarr
Nov 23 '18 at 20:16
1
Works like a charm! Thanks @QHarr
– Steve B
Nov 23 '18 at 21:13
You are most welcome
– QHarr
Nov 23 '18 at 21:14
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%2f53451552%2ffind-the-sum-of-cell-array-only-if-the-row-contains-a-specific-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Maybe
=SUMPRODUCT((C7:C14="Apple")*(E7:AZ14))
answered Nov 23 '18 at 19:18
QHarrQHarr
32.1k82042
@ForwardEd You are absolutely correct.
– QHarr
Nov 23 '18 at 20:16
1
Works like a charm! Thanks @QHarr
– Steve B
Nov 23 '18 at 21:13
You are most welcome
– QHarr
Nov 23 '18 at 21:14
add a comment |
Maybe
=SUMPRODUCT((C7:C14="Apple")*(E7:AZ14))
answered Nov 23 '18 at 19:18
QHarrQHarr
32.1k82042
@ForwardEd You are absolutely correct.
– QHarr
Nov 23 '18 at 20:16
1
Works like a charm! Thanks @QHarr
– Steve B
Nov 23 '18 at 21:13
You are most welcome
– QHarr
Nov 23 '18 at 21:14
add a comment |
Maybe
=SUMPRODUCT((C7:C14="Apple")*(E7:AZ14))
answered Nov 23 '18 at 19:18
QHarrQHarr
32.1k82042
Maybe
=SUMPRODUCT((C7:C14="Apple")*(E7:AZ14))
answered Nov 23 '18 at 19:18
QHarrQHarr
32.1k82042
edited Nov 23 '18 at 20:16
answered Nov 23 '18 at 19:18
QHarrQHarr
32.1k82042
answered Nov 23 '18 at 19:18
QHarrQHarr
32.1k82042
answered Nov 23 '18 at 19:18
QHarrQHarr
32.1k82042
32.1k82042
@ForwardEd You are absolutely correct.
– QHarr
Nov 23 '18 at 20:16
1
Works like a charm! Thanks @QHarr
– Steve B
Nov 23 '18 at 21:13
You are most welcome
– QHarr
Nov 23 '18 at 21:14
add a comment |
@ForwardEd You are absolutely correct.
– QHarr
Nov 23 '18 at 20:16
1
Works like a charm! Thanks @QHarr
– Steve B
Nov 23 '18 at 21:13
You are most welcome
– QHarr
Nov 23 '18 at 21:14
@ForwardEd You are absolutely correct.
– QHarr
Nov 23 '18 at 20:16
@ForwardEd You are absolutely correct.
– QHarr
Nov 23 '18 at 20:16
1
1
Works like a charm! Thanks @QHarr
– Steve B
Nov 23 '18 at 21:13
Works like a charm! Thanks @QHarr
– Steve B
Nov 23 '18 at 21:13
You are most welcome
– QHarr
Nov 23 '18 at 21:14
You are most welcome
– QHarr
Nov 23 '18 at 21:14
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%2f53451552%2ffind-the-sum-of-cell-array-only-if-the-row-contains-a-specific-value%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
