Sum of Products if label is found in a list
I am trying to add together the products of all numbers whose labels appear in a list. For example, in the picture provided, we want the sum of the products of the numbers whose labels are a, e, c, or z. This would be 1*2+5*6+3*4=44.
I thought the formula in the picture would work because we would be taking the sum of the products of the first number and either the second number if the label is in the list, or zero otherwise, but I 'm not really sure why this doesn't work.
excel excel-formula
asked Nov 12 '18 at 21:03
Everyone_ElseEveryone_Else
2,52031535
add a comment |
I am trying to add together the products of all numbers whose labels appear in a list. For example, in the picture provided, we want the sum of the products of the numbers whose labels are a, e, c, or z. This would be 1*2+5*6+3*4=44.
I thought the formula in the picture would work because we would be taking the sum of the products of the first number and either the second number if the label is in the list, or zero otherwise, but I 'm not really sure why this doesn't work.
excel excel-formula
asked Nov 12 '18 at 21:03
Everyone_ElseEveryone_Else
2,52031535
1
Enter the formula with Ctrl + Shift + Enter so the if countif is properly evaluated.
– QHarr
Nov 12 '18 at 21:10
... That is the solution. If you wanna post as an answer, I can accept it.
– Everyone_Else
Nov 12 '18 at 21:14
Have posted then
– QHarr
Nov 12 '18 at 21:21
add a comment |
I am trying to add together the products of all numbers whose labels appear in a list. For example, in the picture provided, we want the sum of the products of the numbers whose labels are a, e, c, or z. This would be 1*2+5*6+3*4=44.
I thought the formula in the picture would work because we would be taking the sum of the products of the first number and either the second number if the label is in the list, or zero otherwise, but I 'm not really sure why this doesn't work.
excel excel-formula
asked Nov 12 '18 at 21:03
Everyone_ElseEveryone_Else
2,52031535
I am trying to add together the products of all numbers whose labels appear in a list. For example, in the picture provided, we want the sum of the products of the numbers whose labels are a, e, c, or z. This would be 1*2+5*6+3*4=44.
I thought the formula in the picture would work because we would be taking the sum of the products of the first number and either the second number if the label is in the list, or zero otherwise, but I 'm not really sure why this doesn't work.
excel excel-formula
excel excel-formula
asked Nov 12 '18 at 21:03
Everyone_ElseEveryone_Else
2,52031535
asked Nov 12 '18 at 21:03
Everyone_ElseEveryone_Else
2,52031535
asked Nov 12 '18 at 21:03
Everyone_ElseEveryone_Else
2,52031535
asked Nov 12 '18 at 21:03
Everyone_ElseEveryone_Else
2,52031535
asked Nov 12 '18 at 21:03
Everyone_ElseEveryone_Else
2,52031535
2,52031535
1
Enter the formula with Ctrl + Shift + Enter so the if countif is properly evaluated.
– QHarr
Nov 12 '18 at 21:10
... That is the solution. If you wanna post as an answer, I can accept it.
– Everyone_Else
Nov 12 '18 at 21:14
Have posted then
– QHarr
Nov 12 '18 at 21:21
add a comment |
1
Enter the formula with Ctrl + Shift + Enter so the if countif is properly evaluated.
– QHarr
Nov 12 '18 at 21:10
... That is the solution. If you wanna post as an answer, I can accept it.
– Everyone_Else
Nov 12 '18 at 21:14
Have posted then
– QHarr
Nov 12 '18 at 21:21
1
1
Enter the formula with Ctrl + Shift + Enter so the if countif is properly evaluated.
– QHarr
Nov 12 '18 at 21:10
Enter the formula with Ctrl + Shift + Enter so the if countif is properly evaluated.
– QHarr
Nov 12 '18 at 21:10
... That is the solution. If you wanna post as an answer, I can accept it.
– Everyone_Else
Nov 12 '18 at 21:14
... That is the solution. If you wanna post as an answer, I can accept it.
– Everyone_Else
Nov 12 '18 at 21:14
Have posted then
– QHarr
Nov 12 '18 at 21:21
Have posted then
– QHarr
Nov 12 '18 at 21:21
add a comment |
1 Answer
1
active
oldest
votes
You need to enter the formula with Ctrl + Shift + Enter so the if countif is properly evaluated. That way you have two arrays of equal length for SUMPRODUCT.
answered Nov 12 '18 at 21:15
QHarrQHarr
34.8k82044
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%2f53270061%2fsum-of-products-if-label-is-found-in-a-list%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
You need to enter the formula with Ctrl + Shift + Enter so the if countif is properly evaluated. That way you have two arrays of equal length for SUMPRODUCT.
answered Nov 12 '18 at 21:15
QHarrQHarr
34.8k82044
add a comment |
You need to enter the formula with Ctrl + Shift + Enter so the if countif is properly evaluated. That way you have two arrays of equal length for SUMPRODUCT.
answered Nov 12 '18 at 21:15
QHarrQHarr
34.8k82044
add a comment |
You need to enter the formula with Ctrl + Shift + Enter so the if countif is properly evaluated. That way you have two arrays of equal length for SUMPRODUCT.
answered Nov 12 '18 at 21:15
QHarrQHarr
34.8k82044
You need to enter the formula with Ctrl + Shift + Enter so the if countif is properly evaluated. That way you have two arrays of equal length for SUMPRODUCT.
answered Nov 12 '18 at 21:15
QHarrQHarr
34.8k82044
answered Nov 12 '18 at 21:15
QHarrQHarr
34.8k82044
answered Nov 12 '18 at 21:15
QHarrQHarr
34.8k82044
answered Nov 12 '18 at 21:15
QHarrQHarr
34.8k82044
34.8k82044
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%2f53270061%2fsum-of-products-if-label-is-found-in-a-list%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
1
Enter the formula with Ctrl + Shift + Enter so the if countif is properly evaluated.
– QHarr
Nov 12 '18 at 21:10
... That is the solution. If you wanna post as an answer, I can accept it.
– Everyone_Else
Nov 12 '18 at 21:14
Have posted then
– QHarr
Nov 12 '18 at 21:21