Get the count of the first column in multiple sheets and display them in separate sheet
I'm dealing with Excel VLOOKUP and COUNTIF that gets the count of number of entries in first column of every sheet and display the results in separate sheet accordingly with the product
Example:
I have 4 sheets in excel (4 sheet related to 4 products say oldnavy/purell/BB/Macys)
Each sheet has multiple columns.
Im looking to get just get the count of number of rows in first column for all 4 sheets and place in fift sheet with product name in each row and count of it
Tried using VLOOKUP and COUNTIF
Any help would be greatly appreciated
excel vba vlookup countif
edited Jan 21 at 7:45
Pᴇʜ
21.3k42750
asked Jan 18 at 17:35
RczoneRczone
13812
add a comment |
I'm dealing with Excel VLOOKUP and COUNTIF that gets the count of number of entries in first column of every sheet and display the results in separate sheet accordingly with the product
Example:
I have 4 sheets in excel (4 sheet related to 4 products say oldnavy/purell/BB/Macys)
Each sheet has multiple columns.
Im looking to get just get the count of number of rows in first column for all 4 sheets and place in fift sheet with product name in each row and count of it
Tried using VLOOKUP and COUNTIF
Any help would be greatly appreciated
excel vba vlookup countif
edited Jan 21 at 7:45
Pᴇʜ
21.3k42750
asked Jan 18 at 17:35
RczoneRczone
13812
add a comment |
I'm dealing with Excel VLOOKUP and COUNTIF that gets the count of number of entries in first column of every sheet and display the results in separate sheet accordingly with the product
Example:
I have 4 sheets in excel (4 sheet related to 4 products say oldnavy/purell/BB/Macys)
Each sheet has multiple columns.
Im looking to get just get the count of number of rows in first column for all 4 sheets and place in fift sheet with product name in each row and count of it
Tried using VLOOKUP and COUNTIF
Any help would be greatly appreciated
excel vba vlookup countif
edited Jan 21 at 7:45
Pᴇʜ
21.3k42750
asked Jan 18 at 17:35
RczoneRczone
13812
I'm dealing with Excel VLOOKUP and COUNTIF that gets the count of number of entries in first column of every sheet and display the results in separate sheet accordingly with the product
Example:
I have 4 sheets in excel (4 sheet related to 4 products say oldnavy/purell/BB/Macys)
Each sheet has multiple columns.
Im looking to get just get the count of number of rows in first column for all 4 sheets and place in fift sheet with product name in each row and count of it
Tried using VLOOKUP and COUNTIF
Any help would be greatly appreciated
excel vba vlookup countif
excel vba vlookup countif
edited Jan 21 at 7:45
Pᴇʜ
21.3k42750
asked Jan 18 at 17:35
RczoneRczone
13812
edited Jan 21 at 7:45
Pᴇʜ
21.3k42750
asked Jan 18 at 17:35
RczoneRczone
13812
edited Jan 21 at 7:45
Pᴇʜ
21.3k42750
edited Jan 21 at 7:45
Pᴇʜ
21.3k42750
edited Jan 21 at 7:45
Pᴇʜ
21.3k42750
21.3k42750
asked Jan 18 at 17:35
RczoneRczone
13812
asked Jan 18 at 17:35
RczoneRczone
13812
asked Jan 18 at 17:35
RczoneRczone
13812
13812
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
If you're trying to dynamically reference worksheets, you need to use INDIRECT().
If, in Sheet5 you have your worksheet names "oldnavy/purell/BB/Macys" in A1:A4 respectively, then in cell B1 you could put
=COUNTA(INDIRECT("'" & A1 & "'!A:A"))
And drag it down. COUNTA counts all non-blank cells in a range.
answered Jan 18 at 17:50
Tate GarringerTate Garringer
68917
Tried this im getting 1 as count @Tate Garringer
– Rczone
Jan 18 at 18:31
Generally that means that it's throwing a#REFerror for what you're trying to reference. The values inA1:A4Will have to match the names of the worksheets otherwise it'll throw that error.
– Tate Garringer
Jan 18 at 18:32
Hi @Tate Garringer see the pics -- first pic where i got summary of counts and second pic is the first coulum of every sheet ibb.co/7SVRmxh ibb.co/qYyHWwt
– Rczone
Jan 18 at 18:43
Edited the answer to accommodate worksheets with spaces in the name.
– Tate Garringer
Jan 18 at 18:48
Hello Thanks Much it worked everysheet except first sheet(sheet which i shared in the pic2) weird..also can we ignore the header im expecting count without header (the heading of the column A1) @Tate Garringer
– Rczone
Jan 18 at 18:59
|
show 1 more comment
If I understand correctly, do this:
=COUNTIF(Sheet1!A:A,"<>")
I believe you understand COUNTIF()
Condition "<>" basically means 'not equal to empty/nothing', so 'count all that is not empty'
Hope that helps
answered Jan 18 at 17:44
FilcukFilcuk
238
Note this will count zero 0 values, as 0 <> "". You can useCOUNTIFS()with an extra condition for that.
– Filcuk
Jan 18 at 17:47
Hello its asking to open another sheet...all my sheets are in single excel.i have input the formule against oldnavy and dragged for rest
– Rczone
Jan 18 at 18:32
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%2f54258893%2fget-the-count-of-the-first-column-in-multiple-sheets-and-display-them-in-separat%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you're trying to dynamically reference worksheets, you need to use INDIRECT().
If, in Sheet5 you have your worksheet names "oldnavy/purell/BB/Macys" in A1:A4 respectively, then in cell B1 you could put
=COUNTA(INDIRECT("'" & A1 & "'!A:A"))
And drag it down. COUNTA counts all non-blank cells in a range.
answered Jan 18 at 17:50
Tate GarringerTate Garringer
68917
Tried this im getting 1 as count @Tate Garringer
– Rczone
Jan 18 at 18:31
Generally that means that it's throwing a#REFerror for what you're trying to reference. The values inA1:A4Will have to match the names of the worksheets otherwise it'll throw that error.
– Tate Garringer
Jan 18 at 18:32
Hi @Tate Garringer see the pics -- first pic where i got summary of counts and second pic is the first coulum of every sheet ibb.co/7SVRmxh ibb.co/qYyHWwt
– Rczone
Jan 18 at 18:43
Edited the answer to accommodate worksheets with spaces in the name.
– Tate Garringer
Jan 18 at 18:48
Hello Thanks Much it worked everysheet except first sheet(sheet which i shared in the pic2) weird..also can we ignore the header im expecting count without header (the heading of the column A1) @Tate Garringer
– Rczone
Jan 18 at 18:59
|
show 1 more comment
If you're trying to dynamically reference worksheets, you need to use INDIRECT().
If, in Sheet5 you have your worksheet names "oldnavy/purell/BB/Macys" in A1:A4 respectively, then in cell B1 you could put
=COUNTA(INDIRECT("'" & A1 & "'!A:A"))
And drag it down. COUNTA counts all non-blank cells in a range.
answered Jan 18 at 17:50
Tate GarringerTate Garringer
68917
Tried this im getting 1 as count @Tate Garringer
– Rczone
Jan 18 at 18:31
Generally that means that it's throwing a#REFerror for what you're trying to reference. The values inA1:A4Will have to match the names of the worksheets otherwise it'll throw that error.
– Tate Garringer
Jan 18 at 18:32
Hi @Tate Garringer see the pics -- first pic where i got summary of counts and second pic is the first coulum of every sheet ibb.co/7SVRmxh ibb.co/qYyHWwt
– Rczone
Jan 18 at 18:43
Edited the answer to accommodate worksheets with spaces in the name.
– Tate Garringer
Jan 18 at 18:48
Hello Thanks Much it worked everysheet except first sheet(sheet which i shared in the pic2) weird..also can we ignore the header im expecting count without header (the heading of the column A1) @Tate Garringer
– Rczone
Jan 18 at 18:59
|
show 1 more comment
If you're trying to dynamically reference worksheets, you need to use INDIRECT().
If, in Sheet5 you have your worksheet names "oldnavy/purell/BB/Macys" in A1:A4 respectively, then in cell B1 you could put
=COUNTA(INDIRECT("'" & A1 & "'!A:A"))
And drag it down. COUNTA counts all non-blank cells in a range.
answered Jan 18 at 17:50
Tate GarringerTate Garringer
68917
If you're trying to dynamically reference worksheets, you need to use INDIRECT().
If, in Sheet5 you have your worksheet names "oldnavy/purell/BB/Macys" in A1:A4 respectively, then in cell B1 you could put
=COUNTA(INDIRECT("'" & A1 & "'!A:A"))
And drag it down. COUNTA counts all non-blank cells in a range.
answered Jan 18 at 17:50
Tate GarringerTate Garringer
68917
edited Jan 18 at 18:48
answered Jan 18 at 17:50
Tate GarringerTate Garringer
68917
answered Jan 18 at 17:50
Tate GarringerTate Garringer
68917
answered Jan 18 at 17:50
Tate GarringerTate Garringer
68917
68917
Tried this im getting 1 as count @Tate Garringer
– Rczone
Jan 18 at 18:31
Generally that means that it's throwing a#REFerror for what you're trying to reference. The values inA1:A4Will have to match the names of the worksheets otherwise it'll throw that error.
– Tate Garringer
Jan 18 at 18:32
Hi @Tate Garringer see the pics -- first pic where i got summary of counts and second pic is the first coulum of every sheet ibb.co/7SVRmxh ibb.co/qYyHWwt
– Rczone
Jan 18 at 18:43
Edited the answer to accommodate worksheets with spaces in the name.
– Tate Garringer
Jan 18 at 18:48
Hello Thanks Much it worked everysheet except first sheet(sheet which i shared in the pic2) weird..also can we ignore the header im expecting count without header (the heading of the column A1) @Tate Garringer
– Rczone
Jan 18 at 18:59
|
show 1 more comment
Tried this im getting 1 as count @Tate Garringer
– Rczone
Jan 18 at 18:31
Generally that means that it's throwing a#REFerror for what you're trying to reference. The values inA1:A4Will have to match the names of the worksheets otherwise it'll throw that error.
– Tate Garringer
Jan 18 at 18:32
Hi @Tate Garringer see the pics -- first pic where i got summary of counts and second pic is the first coulum of every sheet ibb.co/7SVRmxh ibb.co/qYyHWwt
– Rczone
Jan 18 at 18:43
Edited the answer to accommodate worksheets with spaces in the name.
– Tate Garringer
Jan 18 at 18:48
Hello Thanks Much it worked everysheet except first sheet(sheet which i shared in the pic2) weird..also can we ignore the header im expecting count without header (the heading of the column A1) @Tate Garringer
– Rczone
Jan 18 at 18:59
Tried this im getting 1 as count @Tate Garringer
– Rczone
Jan 18 at 18:31
Tried this im getting 1 as count @Tate Garringer
– Rczone
Jan 18 at 18:31
Generally that means that it's throwing a
#REF error for what you're trying to reference. The values in A1:A4 Will have to match the names of the worksheets otherwise it'll throw that error.– Tate Garringer
Jan 18 at 18:32
Generally that means that it's throwing a
#REF error for what you're trying to reference. The values in A1:A4 Will have to match the names of the worksheets otherwise it'll throw that error.– Tate Garringer
Jan 18 at 18:32
Hi @Tate Garringer see the pics -- first pic where i got summary of counts and second pic is the first coulum of every sheet ibb.co/7SVRmxh ibb.co/qYyHWwt
– Rczone
Jan 18 at 18:43
Hi @Tate Garringer see the pics -- first pic where i got summary of counts and second pic is the first coulum of every sheet ibb.co/7SVRmxh ibb.co/qYyHWwt
– Rczone
Jan 18 at 18:43
Edited the answer to accommodate worksheets with spaces in the name.
– Tate Garringer
Jan 18 at 18:48
Edited the answer to accommodate worksheets with spaces in the name.
– Tate Garringer
Jan 18 at 18:48
Hello Thanks Much it worked everysheet except first sheet(sheet which i shared in the pic2) weird..also can we ignore the header im expecting count without header (the heading of the column A1) @Tate Garringer
– Rczone
Jan 18 at 18:59
Hello Thanks Much it worked everysheet except first sheet(sheet which i shared in the pic2) weird..also can we ignore the header im expecting count without header (the heading of the column A1) @Tate Garringer
– Rczone
Jan 18 at 18:59
|
show 1 more comment
If I understand correctly, do this:
=COUNTIF(Sheet1!A:A,"<>")
I believe you understand COUNTIF()
Condition "<>" basically means 'not equal to empty/nothing', so 'count all that is not empty'
Hope that helps
answered Jan 18 at 17:44
FilcukFilcuk
238
Note this will count zero 0 values, as 0 <> "". You can useCOUNTIFS()with an extra condition for that.
– Filcuk
Jan 18 at 17:47
Hello its asking to open another sheet...all my sheets are in single excel.i have input the formule against oldnavy and dragged for rest
– Rczone
Jan 18 at 18:32
add a comment |
If I understand correctly, do this:
=COUNTIF(Sheet1!A:A,"<>")
I believe you understand COUNTIF()
Condition "<>" basically means 'not equal to empty/nothing', so 'count all that is not empty'
Hope that helps
answered Jan 18 at 17:44
FilcukFilcuk
238
Note this will count zero 0 values, as 0 <> "". You can useCOUNTIFS()with an extra condition for that.
– Filcuk
Jan 18 at 17:47
Hello its asking to open another sheet...all my sheets are in single excel.i have input the formule against oldnavy and dragged for rest
– Rczone
Jan 18 at 18:32
add a comment |
If I understand correctly, do this:
=COUNTIF(Sheet1!A:A,"<>")
I believe you understand COUNTIF()
Condition "<>" basically means 'not equal to empty/nothing', so 'count all that is not empty'
Hope that helps
answered Jan 18 at 17:44
FilcukFilcuk
238
If I understand correctly, do this:
=COUNTIF(Sheet1!A:A,"<>")
I believe you understand COUNTIF()
Condition "<>" basically means 'not equal to empty/nothing', so 'count all that is not empty'
Hope that helps
answered Jan 18 at 17:44
FilcukFilcuk
238
answered Jan 18 at 17:44
FilcukFilcuk
238
answered Jan 18 at 17:44
FilcukFilcuk
238
answered Jan 18 at 17:44
FilcukFilcuk
238
238
Note this will count zero 0 values, as 0 <> "". You can useCOUNTIFS()with an extra condition for that.
– Filcuk
Jan 18 at 17:47
Hello its asking to open another sheet...all my sheets are in single excel.i have input the formule against oldnavy and dragged for rest
– Rczone
Jan 18 at 18:32
add a comment |
Note this will count zero 0 values, as 0 <> "". You can useCOUNTIFS()with an extra condition for that.
– Filcuk
Jan 18 at 17:47
Hello its asking to open another sheet...all my sheets are in single excel.i have input the formule against oldnavy and dragged for rest
– Rczone
Jan 18 at 18:32
Note this will count zero 0 values, as 0 <> "". You can use
COUNTIFS() with an extra condition for that.– Filcuk
Jan 18 at 17:47
Note this will count zero 0 values, as 0 <> "". You can use
COUNTIFS() with an extra condition for that.– Filcuk
Jan 18 at 17:47
Hello its asking to open another sheet...all my sheets are in single excel.i have input the formule against oldnavy and dragged for rest
– Rczone
Jan 18 at 18:32
Hello its asking to open another sheet...all my sheets are in single excel.i have input the formule against oldnavy and dragged for rest
– Rczone
Jan 18 at 18:32
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%2f54258893%2fget-the-count-of-the-first-column-in-multiple-sheets-and-display-them-in-separat%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
