Sum all rows when at least one row meets the criteria
I have a database as follows:
I am having issues trying to sum all the quantities by groups when the “Included” flag is Y for at least one of the groups; that is: if there’s a Y on at least one row from group 3 , sum all the rows from group 3, regardless if the rest are not included.
The output should be like this:
Thanks!
excel
edited Nov 14 '18 at 18:00
BruceWayne
17.6k113060
asked Nov 14 '18 at 17:52
George George
11
add a comment |
I have a database as follows:
I am having issues trying to sum all the quantities by groups when the “Included” flag is Y for at least one of the groups; that is: if there’s a Y on at least one row from group 3 , sum all the rows from group 3, regardless if the rest are not included.
The output should be like this:
Thanks!
excel
edited Nov 14 '18 at 18:00
BruceWayne
17.6k113060
asked Nov 14 '18 at 17:52
George George
11
Please take some time to learn about Stack Overflow by taking the tour, reading about how to ask a question, how to answer a question, how to accept answers, etc. You will get much more out of this site, and it will serve you well if you serve it well.
– StoneGiant
Nov 14 '18 at 18:54
I'm not sure why your question was down voted, and it's kind of rude for people to do that without telling you what to fix. It might appear that you have done no work, but I can see that you have, so no problem there. It would help to have your sample data as text instead of images, but I don't think that's a reason to down vote. Having text allows people to copy it more easily and create a test environment. Some people like to see an actual question, but I feel like your "question" is clear enough.
– StoneGiant
Nov 14 '18 at 22:09
add a comment |
I have a database as follows:
I am having issues trying to sum all the quantities by groups when the “Included” flag is Y for at least one of the groups; that is: if there’s a Y on at least one row from group 3 , sum all the rows from group 3, regardless if the rest are not included.
The output should be like this:
Thanks!
excel
edited Nov 14 '18 at 18:00
BruceWayne
17.6k113060
asked Nov 14 '18 at 17:52
George George
11
I have a database as follows:
I am having issues trying to sum all the quantities by groups when the “Included” flag is Y for at least one of the groups; that is: if there’s a Y on at least one row from group 3 , sum all the rows from group 3, regardless if the rest are not included.
The output should be like this:
Thanks!
excel
excel
edited Nov 14 '18 at 18:00
BruceWayne
17.6k113060
asked Nov 14 '18 at 17:52
George George
11
edited Nov 14 '18 at 18:00
BruceWayne
17.6k113060
asked Nov 14 '18 at 17:52
George George
11
edited Nov 14 '18 at 18:00
BruceWayne
17.6k113060
edited Nov 14 '18 at 18:00
BruceWayne
17.6k113060
edited Nov 14 '18 at 18:00
BruceWayne
17.6k113060
17.6k113060
asked Nov 14 '18 at 17:52
George George
11
asked Nov 14 '18 at 17:52
George George
11
asked Nov 14 '18 at 17:52
George George
11
11
Please take some time to learn about Stack Overflow by taking the tour, reading about how to ask a question, how to answer a question, how to accept answers, etc. You will get much more out of this site, and it will serve you well if you serve it well.
– StoneGiant
Nov 14 '18 at 18:54
I'm not sure why your question was down voted, and it's kind of rude for people to do that without telling you what to fix. It might appear that you have done no work, but I can see that you have, so no problem there. It would help to have your sample data as text instead of images, but I don't think that's a reason to down vote. Having text allows people to copy it more easily and create a test environment. Some people like to see an actual question, but I feel like your "question" is clear enough.
– StoneGiant
Nov 14 '18 at 22:09
add a comment |
Please take some time to learn about Stack Overflow by taking the tour, reading about how to ask a question, how to answer a question, how to accept answers, etc. You will get much more out of this site, and it will serve you well if you serve it well.
– StoneGiant
Nov 14 '18 at 18:54
I'm not sure why your question was down voted, and it's kind of rude for people to do that without telling you what to fix. It might appear that you have done no work, but I can see that you have, so no problem there. It would help to have your sample data as text instead of images, but I don't think that's a reason to down vote. Having text allows people to copy it more easily and create a test environment. Some people like to see an actual question, but I feel like your "question" is clear enough.
– StoneGiant
Nov 14 '18 at 22:09
Please take some time to learn about Stack Overflow by taking the tour, reading about how to ask a question, how to answer a question, how to accept answers, etc. You will get much more out of this site, and it will serve you well if you serve it well.
– StoneGiant
Nov 14 '18 at 18:54
Please take some time to learn about Stack Overflow by taking the tour, reading about how to ask a question, how to answer a question, how to accept answers, etc. You will get much more out of this site, and it will serve you well if you serve it well.
– StoneGiant
Nov 14 '18 at 18:54
I'm not sure why your question was down voted, and it's kind of rude for people to do that without telling you what to fix. It might appear that you have done no work, but I can see that you have, so no problem there. It would help to have your sample data as text instead of images, but I don't think that's a reason to down vote. Having text allows people to copy it more easily and create a test environment. Some people like to see an actual question, but I feel like your "question" is clear enough.
– StoneGiant
Nov 14 '18 at 22:09
I'm not sure why your question was down voted, and it's kind of rude for people to do that without telling you what to fix. It might appear that you have done no work, but I can see that you have, so no problem there. It would help to have your sample data as text instead of images, but I don't think that's a reason to down vote. Having text allows people to copy it more easily and create a test environment. Some people like to see an actual question, but I feel like your "question" is clear enough.
– StoneGiant
Nov 14 '18 at 22:09
add a comment |
1 Answer
1
active
oldest
votes
You're going to need to use an array function to solve this one. To enter and array function, type a function as you normally would then press Ctrl+Shift+Enter instead just Enter. (Apple Keyboards differ, but I don't have Apple.)
I'll explain how I put the function together, then bring it all together in the end.
First, I wanted an array of group numbers that have at least one "Y" in the Included column. That array is created by IF(B2:B14="Y",A2:A14,0). The result would be 0,0,0,0,0,0,3,...etc in the sample case.
Next, I flag every group row that has an entry in the list of group numbers using the MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0) function.
Since that uses an exact match, it will create an error when there is no match. ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0)) gives me a true when there is not a match and a false when there is, so I negate it. NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0)))
Another IF converts this to a 1 or a 0.IF(NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0))),1,0)
Finally, I bring it together with the Quantity using a SUMPRODUCT:=SUMPRODUCT(C2:C14,IF(NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0))),1,0))
Again, remember to Ctrl+Shift+Enter to make this work.
Tested in Excel 2013 and it works fine.
answered Nov 14 '18 at 18:51
StoneGiantStoneGiant
912416
That's what I was looking for! I needed a way to create the 0,1 array but couldn't come up with the solution. However, is there any way to create it without using ISERROR? I need to apply it to a large database and may affect calculation performance. PD: Sorry if i have approached this question rather wrongly, where did I make a mistake? Thanks!
– George
Nov 14 '18 at 21:27
I don't know how to avoid using ISERROR.
– StoneGiant
Nov 14 '18 at 22:06
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%2f53306119%2fsum-all-rows-when-at-least-one-row-meets-the-criteria%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're going to need to use an array function to solve this one. To enter and array function, type a function as you normally would then press Ctrl+Shift+Enter instead just Enter. (Apple Keyboards differ, but I don't have Apple.)
I'll explain how I put the function together, then bring it all together in the end.
First, I wanted an array of group numbers that have at least one "Y" in the Included column. That array is created by IF(B2:B14="Y",A2:A14,0). The result would be 0,0,0,0,0,0,3,...etc in the sample case.
Next, I flag every group row that has an entry in the list of group numbers using the MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0) function.
Since that uses an exact match, it will create an error when there is no match. ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0)) gives me a true when there is not a match and a false when there is, so I negate it. NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0)))
Another IF converts this to a 1 or a 0.IF(NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0))),1,0)
Finally, I bring it together with the Quantity using a SUMPRODUCT:=SUMPRODUCT(C2:C14,IF(NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0))),1,0))
Again, remember to Ctrl+Shift+Enter to make this work.
Tested in Excel 2013 and it works fine.
answered Nov 14 '18 at 18:51
StoneGiantStoneGiant
912416
That's what I was looking for! I needed a way to create the 0,1 array but couldn't come up with the solution. However, is there any way to create it without using ISERROR? I need to apply it to a large database and may affect calculation performance. PD: Sorry if i have approached this question rather wrongly, where did I make a mistake? Thanks!
– George
Nov 14 '18 at 21:27
I don't know how to avoid using ISERROR.
– StoneGiant
Nov 14 '18 at 22:06
add a comment |
You're going to need to use an array function to solve this one. To enter and array function, type a function as you normally would then press Ctrl+Shift+Enter instead just Enter. (Apple Keyboards differ, but I don't have Apple.)
I'll explain how I put the function together, then bring it all together in the end.
First, I wanted an array of group numbers that have at least one "Y" in the Included column. That array is created by IF(B2:B14="Y",A2:A14,0). The result would be 0,0,0,0,0,0,3,...etc in the sample case.
Next, I flag every group row that has an entry in the list of group numbers using the MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0) function.
Since that uses an exact match, it will create an error when there is no match. ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0)) gives me a true when there is not a match and a false when there is, so I negate it. NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0)))
Another IF converts this to a 1 or a 0.IF(NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0))),1,0)
Finally, I bring it together with the Quantity using a SUMPRODUCT:=SUMPRODUCT(C2:C14,IF(NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0))),1,0))
Again, remember to Ctrl+Shift+Enter to make this work.
Tested in Excel 2013 and it works fine.
answered Nov 14 '18 at 18:51
StoneGiantStoneGiant
912416
That's what I was looking for! I needed a way to create the 0,1 array but couldn't come up with the solution. However, is there any way to create it without using ISERROR? I need to apply it to a large database and may affect calculation performance. PD: Sorry if i have approached this question rather wrongly, where did I make a mistake? Thanks!
– George
Nov 14 '18 at 21:27
I don't know how to avoid using ISERROR.
– StoneGiant
Nov 14 '18 at 22:06
add a comment |
You're going to need to use an array function to solve this one. To enter and array function, type a function as you normally would then press Ctrl+Shift+Enter instead just Enter. (Apple Keyboards differ, but I don't have Apple.)
I'll explain how I put the function together, then bring it all together in the end.
First, I wanted an array of group numbers that have at least one "Y" in the Included column. That array is created by IF(B2:B14="Y",A2:A14,0). The result would be 0,0,0,0,0,0,3,...etc in the sample case.
Next, I flag every group row that has an entry in the list of group numbers using the MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0) function.
Since that uses an exact match, it will create an error when there is no match. ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0)) gives me a true when there is not a match and a false when there is, so I negate it. NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0)))
Another IF converts this to a 1 or a 0.IF(NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0))),1,0)
Finally, I bring it together with the Quantity using a SUMPRODUCT:=SUMPRODUCT(C2:C14,IF(NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0))),1,0))
Again, remember to Ctrl+Shift+Enter to make this work.
Tested in Excel 2013 and it works fine.
answered Nov 14 '18 at 18:51
StoneGiantStoneGiant
912416
You're going to need to use an array function to solve this one. To enter and array function, type a function as you normally would then press Ctrl+Shift+Enter instead just Enter. (Apple Keyboards differ, but I don't have Apple.)
I'll explain how I put the function together, then bring it all together in the end.
First, I wanted an array of group numbers that have at least one "Y" in the Included column. That array is created by IF(B2:B14="Y",A2:A14,0). The result would be 0,0,0,0,0,0,3,...etc in the sample case.
Next, I flag every group row that has an entry in the list of group numbers using the MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0) function.
Since that uses an exact match, it will create an error when there is no match. ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0)) gives me a true when there is not a match and a false when there is, so I negate it. NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0)))
Another IF converts this to a 1 or a 0.IF(NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0))),1,0)
Finally, I bring it together with the Quantity using a SUMPRODUCT:=SUMPRODUCT(C2:C14,IF(NOT(ISERROR(MATCH(A2:A14,IF(B2:B14="Y",A2:A14,0),0))),1,0))
Again, remember to Ctrl+Shift+Enter to make this work.
Tested in Excel 2013 and it works fine.
answered Nov 14 '18 at 18:51
StoneGiantStoneGiant
912416
answered Nov 14 '18 at 18:51
StoneGiantStoneGiant
912416
answered Nov 14 '18 at 18:51
StoneGiantStoneGiant
912416
answered Nov 14 '18 at 18:51
StoneGiantStoneGiant
912416
912416
That's what I was looking for! I needed a way to create the 0,1 array but couldn't come up with the solution. However, is there any way to create it without using ISERROR? I need to apply it to a large database and may affect calculation performance. PD: Sorry if i have approached this question rather wrongly, where did I make a mistake? Thanks!
– George
Nov 14 '18 at 21:27
I don't know how to avoid using ISERROR.
– StoneGiant
Nov 14 '18 at 22:06
add a comment |
That's what I was looking for! I needed a way to create the 0,1 array but couldn't come up with the solution. However, is there any way to create it without using ISERROR? I need to apply it to a large database and may affect calculation performance. PD: Sorry if i have approached this question rather wrongly, where did I make a mistake? Thanks!
– George
Nov 14 '18 at 21:27
I don't know how to avoid using ISERROR.
– StoneGiant
Nov 14 '18 at 22:06
That's what I was looking for! I needed a way to create the 0,1 array but couldn't come up with the solution. However, is there any way to create it without using ISERROR? I need to apply it to a large database and may affect calculation performance. PD: Sorry if i have approached this question rather wrongly, where did I make a mistake? Thanks!
– George
Nov 14 '18 at 21:27
That's what I was looking for! I needed a way to create the 0,1 array but couldn't come up with the solution. However, is there any way to create it without using ISERROR? I need to apply it to a large database and may affect calculation performance. PD: Sorry if i have approached this question rather wrongly, where did I make a mistake? Thanks!
– George
Nov 14 '18 at 21:27
I don't know how to avoid using ISERROR.
– StoneGiant
Nov 14 '18 at 22:06
I don't know how to avoid using ISERROR.
– StoneGiant
Nov 14 '18 at 22:06
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%2f53306119%2fsum-all-rows-when-at-least-one-row-meets-the-criteria%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
Please take some time to learn about Stack Overflow by taking the tour, reading about how to ask a question, how to answer a question, how to accept answers, etc. You will get much more out of this site, and it will serve you well if you serve it well.
– StoneGiant
Nov 14 '18 at 18:54
I'm not sure why your question was down voted, and it's kind of rude for people to do that without telling you what to fix. It might appear that you have done no work, but I can see that you have, so no problem there. It would help to have your sample data as text instead of images, but I don't think that's a reason to down vote. Having text allows people to copy it more easily and create a test environment. Some people like to see an actual question, but I feel like your "question" is clear enough.
– StoneGiant
Nov 14 '18 at 22:09