COUNTIF/SUMIF using a dynamically set lookup range based on a named value
I am looking to count how many of times a product was ordered. Simple enough if I was able to hard code the columns that correspond with their products but the columns often change. The pictures below give an idea of the data coming in, and my desired results. I want to count up the "Y" for each product and record how many different orders contained that item.
DISCLOSURE: My actual data set is a lot more complicated with thousands of records and hundreds of columns and not about fruit, but made this example for simplicity.
https://imgur.com/a/X5wtdh6
As you can see in this second example, the products are not the same or in the same order:
https://imgur.com/a/7TNmaHW
What I'm trying to do is create a formula that will check which column that product exists in, for example Oranges, and then sum up all the "Y" that are within that column. The product will at max be present as a column header once per file (sometimes it won't be in the file at all).
When manually gathering the values, I use this formula on a second sheet to create the summary table (using Oranges from Sample 1 as an example) =COUNTIF(Sheet1!B:B,"Y"). I manually change the column it is referencing each day we receive a new file. As you can imagine, this is very time-consuming.
In short, I am looking to replace the "Sheet1!B:B" part of the COUNTIF formula with a SEARCH (or equivalent) function to look which column contains Oranges. I am also open to a VBA solution, but am not as familiar with it.
Please let me know if you require any more clarification.
Thank you.
excel vba excel-vba excel-formula
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
asked Nov 12 '18 at 20:37
MattMatt
466
add a comment |
I am looking to count how many of times a product was ordered. Simple enough if I was able to hard code the columns that correspond with their products but the columns often change. The pictures below give an idea of the data coming in, and my desired results. I want to count up the "Y" for each product and record how many different orders contained that item.
DISCLOSURE: My actual data set is a lot more complicated with thousands of records and hundreds of columns and not about fruit, but made this example for simplicity.
https://imgur.com/a/X5wtdh6
As you can see in this second example, the products are not the same or in the same order:
https://imgur.com/a/7TNmaHW
What I'm trying to do is create a formula that will check which column that product exists in, for example Oranges, and then sum up all the "Y" that are within that column. The product will at max be present as a column header once per file (sometimes it won't be in the file at all).
When manually gathering the values, I use this formula on a second sheet to create the summary table (using Oranges from Sample 1 as an example) =COUNTIF(Sheet1!B:B,"Y"). I manually change the column it is referencing each day we receive a new file. As you can imagine, this is very time-consuming.
In short, I am looking to replace the "Sheet1!B:B" part of the COUNTIF formula with a SEARCH (or equivalent) function to look which column contains Oranges. I am also open to a VBA solution, but am not as familiar with it.
Please let me know if you require any more clarification.
Thank you.
excel vba excel-vba excel-formula
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
asked Nov 12 '18 at 20:37
MattMatt
466
2
have you considered match() to find the column heading and indirect() to build the countif()?
– Solar Mike
Nov 12 '18 at 20:40
I was trying that with no luck, but I finally stumbled upon a solution using the below: =COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")
– Matt
Nov 12 '18 at 20:52
add a comment |
I am looking to count how many of times a product was ordered. Simple enough if I was able to hard code the columns that correspond with their products but the columns often change. The pictures below give an idea of the data coming in, and my desired results. I want to count up the "Y" for each product and record how many different orders contained that item.
DISCLOSURE: My actual data set is a lot more complicated with thousands of records and hundreds of columns and not about fruit, but made this example for simplicity.
https://imgur.com/a/X5wtdh6
As you can see in this second example, the products are not the same or in the same order:
https://imgur.com/a/7TNmaHW
What I'm trying to do is create a formula that will check which column that product exists in, for example Oranges, and then sum up all the "Y" that are within that column. The product will at max be present as a column header once per file (sometimes it won't be in the file at all).
When manually gathering the values, I use this formula on a second sheet to create the summary table (using Oranges from Sample 1 as an example) =COUNTIF(Sheet1!B:B,"Y"). I manually change the column it is referencing each day we receive a new file. As you can imagine, this is very time-consuming.
In short, I am looking to replace the "Sheet1!B:B" part of the COUNTIF formula with a SEARCH (or equivalent) function to look which column contains Oranges. I am also open to a VBA solution, but am not as familiar with it.
Please let me know if you require any more clarification.
Thank you.
excel vba excel-vba excel-formula
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
asked Nov 12 '18 at 20:37
MattMatt
466
I am looking to count how many of times a product was ordered. Simple enough if I was able to hard code the columns that correspond with their products but the columns often change. The pictures below give an idea of the data coming in, and my desired results. I want to count up the "Y" for each product and record how many different orders contained that item.
DISCLOSURE: My actual data set is a lot more complicated with thousands of records and hundreds of columns and not about fruit, but made this example for simplicity.
https://imgur.com/a/X5wtdh6
As you can see in this second example, the products are not the same or in the same order:
https://imgur.com/a/7TNmaHW
What I'm trying to do is create a formula that will check which column that product exists in, for example Oranges, and then sum up all the "Y" that are within that column. The product will at max be present as a column header once per file (sometimes it won't be in the file at all).
When manually gathering the values, I use this formula on a second sheet to create the summary table (using Oranges from Sample 1 as an example) =COUNTIF(Sheet1!B:B,"Y"). I manually change the column it is referencing each day we receive a new file. As you can imagine, this is very time-consuming.
In short, I am looking to replace the "Sheet1!B:B" part of the COUNTIF formula with a SEARCH (or equivalent) function to look which column contains Oranges. I am also open to a VBA solution, but am not as familiar with it.
Please let me know if you require any more clarification.
Thank you.
excel vba excel-vba excel-formula
excel vba excel-vba excel-formula
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
asked Nov 12 '18 at 20:37
MattMatt
466
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
asked Nov 12 '18 at 20:37
MattMatt
466
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
20.9k42650
asked Nov 12 '18 at 20:37
MattMatt
466
asked Nov 12 '18 at 20:37
MattMatt
466
asked Nov 12 '18 at 20:37
MattMatt
466
466
2
have you considered match() to find the column heading and indirect() to build the countif()?
– Solar Mike
Nov 12 '18 at 20:40
I was trying that with no luck, but I finally stumbled upon a solution using the below: =COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")
– Matt
Nov 12 '18 at 20:52
add a comment |
2
have you considered match() to find the column heading and indirect() to build the countif()?
– Solar Mike
Nov 12 '18 at 20:40
I was trying that with no luck, but I finally stumbled upon a solution using the below: =COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")
– Matt
Nov 12 '18 at 20:52
2
2
have you considered match() to find the column heading and indirect() to build the countif()?
– Solar Mike
Nov 12 '18 at 20:40
have you considered match() to find the column heading and indirect() to build the countif()?
– Solar Mike
Nov 12 '18 at 20:40
I was trying that with no luck, but I finally stumbled upon a solution using the below: =COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")
– Matt
Nov 12 '18 at 20:52
I was trying that with no luck, but I finally stumbled upon a solution using the below: =COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")
– Matt
Nov 12 '18 at 20:52
add a comment |
1 Answer
1
active
oldest
votes
Was able to find this solution:
=COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
answered Nov 12 '18 at 20:53
MattMatt
466
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%2f53269714%2fcountif-sumif-using-a-dynamically-set-lookup-range-based-on-a-named-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
Was able to find this solution:
=COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
answered Nov 12 '18 at 20:53
MattMatt
466
add a comment |
Was able to find this solution:
=COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
answered Nov 12 '18 at 20:53
MattMatt
466
add a comment |
Was able to find this solution:
=COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
answered Nov 12 '18 at 20:53
MattMatt
466
Was able to find this solution:
=COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
answered Nov 12 '18 at 20:53
MattMatt
466
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
edited Nov 13 '18 at 7:20
Pᴇʜ
20.9k42650
20.9k42650
answered Nov 12 '18 at 20:53
MattMatt
466
answered Nov 12 '18 at 20:53
MattMatt
466
answered Nov 12 '18 at 20:53
MattMatt
466
466
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%2f53269714%2fcountif-sumif-using-a-dynamically-set-lookup-range-based-on-a-named-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
2
have you considered match() to find the column heading and indirect() to build the countif()?
– Solar Mike
Nov 12 '18 at 20:40
I was trying that with no luck, but I finally stumbled upon a solution using the below: =COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")
– Matt
Nov 12 '18 at 20:52