Using COUNTA and/or COUNTBLANK to show a sum of entries in two columns
Edit:
Current formula
To start, this is for my workbook that I have open throughout the day. One of my responsibilities to to periodically check another workbook for items added by the rest of my team, and resolve them. Columns A-J get filled by the team, and I enter the date in K when the item is completed.
Sample data
On the screenshot for the sample data, I've highlighted the cells I need to count. Right now I have the total entries in column J (not including headers) showing in cell H1 on my workbook that stays up all day (Current Formula image). H1 reads 22, but the correct number would be 3. Unfortunately, using COUNTBLANK in column K (as far as I understand it) would continue counting beyond the last actual entry.
Original:
I'm looking for a little help with figuring out how to combine a couple of formulas. Currently, I'm using the following formula to track how many entries have been made in another workbook:
=COUNTA(' **file directory here**[2019_1_January.xlsx]Sheet1'!$J$3:$J$402)
It works fine so far but I realized that what I really need is to count the number of entries which haven't been resolved yet, so what this would look like on the spreadsheet is a count of all the NOT blank entries in column J which have a blank in column K. It would help if it could also show a 0 if there are no unresolved entries.
I don't know if this information is helpful or relevant to what formulas are available but just in case: both columns are simple mm/dd/yyyy date entries.
microsoft-excel worksheet-function
asked Jan 3 at 16:53
MonkeyDLucyMonkeyDLucy
34
add a comment |
Edit:
Current formula
To start, this is for my workbook that I have open throughout the day. One of my responsibilities to to periodically check another workbook for items added by the rest of my team, and resolve them. Columns A-J get filled by the team, and I enter the date in K when the item is completed.
Sample data
On the screenshot for the sample data, I've highlighted the cells I need to count. Right now I have the total entries in column J (not including headers) showing in cell H1 on my workbook that stays up all day (Current Formula image). H1 reads 22, but the correct number would be 3. Unfortunately, using COUNTBLANK in column K (as far as I understand it) would continue counting beyond the last actual entry.
Original:
I'm looking for a little help with figuring out how to combine a couple of formulas. Currently, I'm using the following formula to track how many entries have been made in another workbook:
=COUNTA(' **file directory here**[2019_1_January.xlsx]Sheet1'!$J$3:$J$402)
It works fine so far but I realized that what I really need is to count the number of entries which haven't been resolved yet, so what this would look like on the spreadsheet is a count of all the NOT blank entries in column J which have a blank in column K. It would help if it could also show a 0 if there are no unresolved entries.
I don't know if this information is helpful or relevant to what formulas are available but just in case: both columns are simple mm/dd/yyyy date entries.
microsoft-excel worksheet-function
asked Jan 3 at 16:53
MonkeyDLucyMonkeyDLucy
34
Can you add a picture of your spreadsheet to your question to help us visualize what you're trying to do? Sounds like you need the COUNTBLANK function. See link. contextures.com/xlFunctions04.html
– user2800
Jan 3 at 17:00
It's a work document with other people's info on it so I can't share it, sorry. I'll try to create a dummy version later if needed. Unfortunately I think COUNTBLANK is only half of what I need, but I'll check out the link. Because this is more or less being done on my personal time, even though it's for work, I don't have the ability to just dive into Google like I usually would :)
– MonkeyDLucy
Jan 3 at 17:06
Are you able to provide example data that isn't commercially sensitive? Ideally we would like to see how it appears currently and the expected output. Please take a look at How to Ask and take our tour.
– Burgi
Jan 3 at 17:26
Understood! I apologize for being unprepared, I definitely didn't do as much research as I usually would. I'll get myself organized and update the question in a few hours when I take my break. Thanks for your patience!
– MonkeyDLucy
Jan 3 at 17:32
I got excited and updated it already :) Hopefully it has fewer holes, but my lunch is over and I have to get back to work!!!
– MonkeyDLucy
Jan 3 at 18:12
add a comment |
Edit:
Current formula
To start, this is for my workbook that I have open throughout the day. One of my responsibilities to to periodically check another workbook for items added by the rest of my team, and resolve them. Columns A-J get filled by the team, and I enter the date in K when the item is completed.
Sample data
On the screenshot for the sample data, I've highlighted the cells I need to count. Right now I have the total entries in column J (not including headers) showing in cell H1 on my workbook that stays up all day (Current Formula image). H1 reads 22, but the correct number would be 3. Unfortunately, using COUNTBLANK in column K (as far as I understand it) would continue counting beyond the last actual entry.
Original:
I'm looking for a little help with figuring out how to combine a couple of formulas. Currently, I'm using the following formula to track how many entries have been made in another workbook:
=COUNTA(' **file directory here**[2019_1_January.xlsx]Sheet1'!$J$3:$J$402)
It works fine so far but I realized that what I really need is to count the number of entries which haven't been resolved yet, so what this would look like on the spreadsheet is a count of all the NOT blank entries in column J which have a blank in column K. It would help if it could also show a 0 if there are no unresolved entries.
I don't know if this information is helpful or relevant to what formulas are available but just in case: both columns are simple mm/dd/yyyy date entries.
microsoft-excel worksheet-function
asked Jan 3 at 16:53
MonkeyDLucyMonkeyDLucy
34
Edit:
Current formula
To start, this is for my workbook that I have open throughout the day. One of my responsibilities to to periodically check another workbook for items added by the rest of my team, and resolve them. Columns A-J get filled by the team, and I enter the date in K when the item is completed.
Sample data
On the screenshot for the sample data, I've highlighted the cells I need to count. Right now I have the total entries in column J (not including headers) showing in cell H1 on my workbook that stays up all day (Current Formula image). H1 reads 22, but the correct number would be 3. Unfortunately, using COUNTBLANK in column K (as far as I understand it) would continue counting beyond the last actual entry.
Original:
I'm looking for a little help with figuring out how to combine a couple of formulas. Currently, I'm using the following formula to track how many entries have been made in another workbook:
=COUNTA(' **file directory here**[2019_1_January.xlsx]Sheet1'!$J$3:$J$402)
It works fine so far but I realized that what I really need is to count the number of entries which haven't been resolved yet, so what this would look like on the spreadsheet is a count of all the NOT blank entries in column J which have a blank in column K. It would help if it could also show a 0 if there are no unresolved entries.
I don't know if this information is helpful or relevant to what formulas are available but just in case: both columns are simple mm/dd/yyyy date entries.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Jan 3 at 16:53
MonkeyDLucyMonkeyDLucy
34
asked Jan 3 at 16:53
MonkeyDLucyMonkeyDLucy
34
edited Jan 3 at 18:24
MonkeyDLucy
asked Jan 3 at 16:53
MonkeyDLucyMonkeyDLucy
34
asked Jan 3 at 16:53
MonkeyDLucyMonkeyDLucy
34
asked Jan 3 at 16:53
MonkeyDLucyMonkeyDLucy
34
34
Can you add a picture of your spreadsheet to your question to help us visualize what you're trying to do? Sounds like you need the COUNTBLANK function. See link. contextures.com/xlFunctions04.html
– user2800
Jan 3 at 17:00
It's a work document with other people's info on it so I can't share it, sorry. I'll try to create a dummy version later if needed. Unfortunately I think COUNTBLANK is only half of what I need, but I'll check out the link. Because this is more or less being done on my personal time, even though it's for work, I don't have the ability to just dive into Google like I usually would :)
– MonkeyDLucy
Jan 3 at 17:06
Are you able to provide example data that isn't commercially sensitive? Ideally we would like to see how it appears currently and the expected output. Please take a look at How to Ask and take our tour.
– Burgi
Jan 3 at 17:26
Understood! I apologize for being unprepared, I definitely didn't do as much research as I usually would. I'll get myself organized and update the question in a few hours when I take my break. Thanks for your patience!
– MonkeyDLucy
Jan 3 at 17:32
I got excited and updated it already :) Hopefully it has fewer holes, but my lunch is over and I have to get back to work!!!
– MonkeyDLucy
Jan 3 at 18:12
add a comment |
Can you add a picture of your spreadsheet to your question to help us visualize what you're trying to do? Sounds like you need the COUNTBLANK function. See link. contextures.com/xlFunctions04.html
– user2800
Jan 3 at 17:00
It's a work document with other people's info on it so I can't share it, sorry. I'll try to create a dummy version later if needed. Unfortunately I think COUNTBLANK is only half of what I need, but I'll check out the link. Because this is more or less being done on my personal time, even though it's for work, I don't have the ability to just dive into Google like I usually would :)
– MonkeyDLucy
Jan 3 at 17:06
Are you able to provide example data that isn't commercially sensitive? Ideally we would like to see how it appears currently and the expected output. Please take a look at How to Ask and take our tour.
– Burgi
Jan 3 at 17:26
Understood! I apologize for being unprepared, I definitely didn't do as much research as I usually would. I'll get myself organized and update the question in a few hours when I take my break. Thanks for your patience!
– MonkeyDLucy
Jan 3 at 17:32
I got excited and updated it already :) Hopefully it has fewer holes, but my lunch is over and I have to get back to work!!!
– MonkeyDLucy
Jan 3 at 18:12
Can you add a picture of your spreadsheet to your question to help us visualize what you're trying to do? Sounds like you need the COUNTBLANK function. See link. contextures.com/xlFunctions04.html
– user2800
Jan 3 at 17:00
Can you add a picture of your spreadsheet to your question to help us visualize what you're trying to do? Sounds like you need the COUNTBLANK function. See link. contextures.com/xlFunctions04.html
– user2800
Jan 3 at 17:00
It's a work document with other people's info on it so I can't share it, sorry. I'll try to create a dummy version later if needed. Unfortunately I think COUNTBLANK is only half of what I need, but I'll check out the link. Because this is more or less being done on my personal time, even though it's for work, I don't have the ability to just dive into Google like I usually would :)
– MonkeyDLucy
Jan 3 at 17:06
It's a work document with other people's info on it so I can't share it, sorry. I'll try to create a dummy version later if needed. Unfortunately I think COUNTBLANK is only half of what I need, but I'll check out the link. Because this is more or less being done on my personal time, even though it's for work, I don't have the ability to just dive into Google like I usually would :)
– MonkeyDLucy
Jan 3 at 17:06
Are you able to provide example data that isn't commercially sensitive? Ideally we would like to see how it appears currently and the expected output. Please take a look at How to Ask and take our tour.
– Burgi
Jan 3 at 17:26
Are you able to provide example data that isn't commercially sensitive? Ideally we would like to see how it appears currently and the expected output. Please take a look at How to Ask and take our tour.
– Burgi
Jan 3 at 17:26
Understood! I apologize for being unprepared, I definitely didn't do as much research as I usually would. I'll get myself organized and update the question in a few hours when I take my break. Thanks for your patience!
– MonkeyDLucy
Jan 3 at 17:32
Understood! I apologize for being unprepared, I definitely didn't do as much research as I usually would. I'll get myself organized and update the question in a few hours when I take my break. Thanks for your patience!
– MonkeyDLucy
Jan 3 at 17:32
I got excited and updated it already :) Hopefully it has fewer holes, but my lunch is over and I have to get back to work!!!
– MonkeyDLucy
Jan 3 at 18:12
I got excited and updated it already :) Hopefully it has fewer holes, but my lunch is over and I have to get back to work!!!
– MonkeyDLucy
Jan 3 at 18:12
add a comment |
1 Answer
1
active
oldest
votes
EDITED
Given that column J is the "Date of Request" column and you only want to count the blanks in column K for those lines that have a value in column J, then this should work:
=COUNTBLANK(' **file directory here**[2019_1_January.xlsx]Sheet1'!$K$3:$K$402) - COUNTBLANK(' **file directory here**[2019_1_January.xlsx]Sheet1'!$J$3:$J$402)
This counts all the blanks in your range K3:K402 and then subtracts the number of blanks found in the range J3:J402 which eliminates the ones "beyond the last actual entry".
answered Jan 3 at 18:53
Rey JunaRey Juna
608111
I think I understand, I do apologize because I didn't realize I had cropped the columns but Date of Request is J so my brain is grinding to make sense of it. I'll give it a shot and let you know!
– MonkeyDLucy
Jan 3 at 18:57
OK, updated the answer based on your clarification. This now assumes that columnKis the column for which you want to count the blanks.
– Rey Juna
Jan 3 at 19:04
It's working! I'm not sure what changed but it was giving a #VALUE error and now it's showing the right number :D
– MonkeyDLucy
Jan 3 at 19:07
Sometimes happens when referencing data from another file.
– Rey Juna
Jan 3 at 19:09
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1390239%2fusing-counta-and-or-countblank-to-show-a-sum-of-entries-in-two-columns%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
EDITED
Given that column J is the "Date of Request" column and you only want to count the blanks in column K for those lines that have a value in column J, then this should work:
=COUNTBLANK(' **file directory here**[2019_1_January.xlsx]Sheet1'!$K$3:$K$402) - COUNTBLANK(' **file directory here**[2019_1_January.xlsx]Sheet1'!$J$3:$J$402)
This counts all the blanks in your range K3:K402 and then subtracts the number of blanks found in the range J3:J402 which eliminates the ones "beyond the last actual entry".
answered Jan 3 at 18:53
Rey JunaRey Juna
608111
I think I understand, I do apologize because I didn't realize I had cropped the columns but Date of Request is J so my brain is grinding to make sense of it. I'll give it a shot and let you know!
– MonkeyDLucy
Jan 3 at 18:57
OK, updated the answer based on your clarification. This now assumes that columnKis the column for which you want to count the blanks.
– Rey Juna
Jan 3 at 19:04
It's working! I'm not sure what changed but it was giving a #VALUE error and now it's showing the right number :D
– MonkeyDLucy
Jan 3 at 19:07
Sometimes happens when referencing data from another file.
– Rey Juna
Jan 3 at 19:09
add a comment |
EDITED
Given that column J is the "Date of Request" column and you only want to count the blanks in column K for those lines that have a value in column J, then this should work:
=COUNTBLANK(' **file directory here**[2019_1_January.xlsx]Sheet1'!$K$3:$K$402) - COUNTBLANK(' **file directory here**[2019_1_January.xlsx]Sheet1'!$J$3:$J$402)
This counts all the blanks in your range K3:K402 and then subtracts the number of blanks found in the range J3:J402 which eliminates the ones "beyond the last actual entry".
answered Jan 3 at 18:53
Rey JunaRey Juna
608111
I think I understand, I do apologize because I didn't realize I had cropped the columns but Date of Request is J so my brain is grinding to make sense of it. I'll give it a shot and let you know!
– MonkeyDLucy
Jan 3 at 18:57
OK, updated the answer based on your clarification. This now assumes that columnKis the column for which you want to count the blanks.
– Rey Juna
Jan 3 at 19:04
It's working! I'm not sure what changed but it was giving a #VALUE error and now it's showing the right number :D
– MonkeyDLucy
Jan 3 at 19:07
Sometimes happens when referencing data from another file.
– Rey Juna
Jan 3 at 19:09
add a comment |
EDITED
Given that column J is the "Date of Request" column and you only want to count the blanks in column K for those lines that have a value in column J, then this should work:
=COUNTBLANK(' **file directory here**[2019_1_January.xlsx]Sheet1'!$K$3:$K$402) - COUNTBLANK(' **file directory here**[2019_1_January.xlsx]Sheet1'!$J$3:$J$402)
This counts all the blanks in your range K3:K402 and then subtracts the number of blanks found in the range J3:J402 which eliminates the ones "beyond the last actual entry".
answered Jan 3 at 18:53
Rey JunaRey Juna
608111
EDITED
Given that column J is the "Date of Request" column and you only want to count the blanks in column K for those lines that have a value in column J, then this should work:
=COUNTBLANK(' **file directory here**[2019_1_January.xlsx]Sheet1'!$K$3:$K$402) - COUNTBLANK(' **file directory here**[2019_1_January.xlsx]Sheet1'!$J$3:$J$402)
This counts all the blanks in your range K3:K402 and then subtracts the number of blanks found in the range J3:J402 which eliminates the ones "beyond the last actual entry".
answered Jan 3 at 18:53
Rey JunaRey Juna
608111
edited Jan 3 at 19:02
answered Jan 3 at 18:53
Rey JunaRey Juna
608111
answered Jan 3 at 18:53
Rey JunaRey Juna
608111
answered Jan 3 at 18:53
Rey JunaRey Juna
608111
608111
I think I understand, I do apologize because I didn't realize I had cropped the columns but Date of Request is J so my brain is grinding to make sense of it. I'll give it a shot and let you know!
– MonkeyDLucy
Jan 3 at 18:57
OK, updated the answer based on your clarification. This now assumes that columnKis the column for which you want to count the blanks.
– Rey Juna
Jan 3 at 19:04
It's working! I'm not sure what changed but it was giving a #VALUE error and now it's showing the right number :D
– MonkeyDLucy
Jan 3 at 19:07
Sometimes happens when referencing data from another file.
– Rey Juna
Jan 3 at 19:09
add a comment |
I think I understand, I do apologize because I didn't realize I had cropped the columns but Date of Request is J so my brain is grinding to make sense of it. I'll give it a shot and let you know!
– MonkeyDLucy
Jan 3 at 18:57
OK, updated the answer based on your clarification. This now assumes that columnKis the column for which you want to count the blanks.
– Rey Juna
Jan 3 at 19:04
It's working! I'm not sure what changed but it was giving a #VALUE error and now it's showing the right number :D
– MonkeyDLucy
Jan 3 at 19:07
Sometimes happens when referencing data from another file.
– Rey Juna
Jan 3 at 19:09
I think I understand, I do apologize because I didn't realize I had cropped the columns but Date of Request is J so my brain is grinding to make sense of it. I'll give it a shot and let you know!
– MonkeyDLucy
Jan 3 at 18:57
I think I understand, I do apologize because I didn't realize I had cropped the columns but Date of Request is J so my brain is grinding to make sense of it. I'll give it a shot and let you know!
– MonkeyDLucy
Jan 3 at 18:57
OK, updated the answer based on your clarification. This now assumes that column
K is the column for which you want to count the blanks.– Rey Juna
Jan 3 at 19:04
OK, updated the answer based on your clarification. This now assumes that column
K is the column for which you want to count the blanks.– Rey Juna
Jan 3 at 19:04
It's working! I'm not sure what changed but it was giving a #VALUE error and now it's showing the right number :D
– MonkeyDLucy
Jan 3 at 19:07
It's working! I'm not sure what changed but it was giving a #VALUE error and now it's showing the right number :D
– MonkeyDLucy
Jan 3 at 19:07
Sometimes happens when referencing data from another file.
– Rey Juna
Jan 3 at 19:09
Sometimes happens when referencing data from another file.
– Rey Juna
Jan 3 at 19:09
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1390239%2fusing-counta-and-or-countblank-to-show-a-sum-of-entries-in-two-columns%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
Can you add a picture of your spreadsheet to your question to help us visualize what you're trying to do? Sounds like you need the COUNTBLANK function. See link. contextures.com/xlFunctions04.html
– user2800
Jan 3 at 17:00
It's a work document with other people's info on it so I can't share it, sorry. I'll try to create a dummy version later if needed. Unfortunately I think COUNTBLANK is only half of what I need, but I'll check out the link. Because this is more or less being done on my personal time, even though it's for work, I don't have the ability to just dive into Google like I usually would :)
– MonkeyDLucy
Jan 3 at 17:06
Are you able to provide example data that isn't commercially sensitive? Ideally we would like to see how it appears currently and the expected output. Please take a look at How to Ask and take our tour.
– Burgi
Jan 3 at 17:26
Understood! I apologize for being unprepared, I definitely didn't do as much research as I usually would. I'll get myself organized and update the question in a few hours when I take my break. Thanks for your patience!
– MonkeyDLucy
Jan 3 at 17:32
I got excited and updated it already :) Hopefully it has fewer holes, but my lunch is over and I have to get back to work!!!
– MonkeyDLucy
Jan 3 at 18:12