countIf with merged cells
Given this sheet, I'd like to count duplicates in column B:
[ ][ A ][ B ][ C ][ D ]
[1][ cat ][ ][ ]
[2][ ][ cat ][ ][ dog ]
[3][ cat ][ bar ]
[4][ ][ hat ][ ][ ]
[5][ ][ cat ][ red ]
[6][ cat ]
[7][ ][ mad ][ ][ ]
[8][ cat ][ cat ][ ][ cat ]
If I use =countif(B1:B7,"cat") Google Sheets only counts cells B2, B5, and B8, and it doesn't count B1, B3, and B6.
Note that I didn't want to count cells A8 and D8, as I specifically only want to count duplicates in column B.
google-sheets google-sheets-formula
edited Nov 22 at 2:44
Rubén
9,99043265
asked Nov 20 at 18:56
DaMaxContent
1065
add a comment |
Given this sheet, I'd like to count duplicates in column B:
[ ][ A ][ B ][ C ][ D ]
[1][ cat ][ ][ ]
[2][ ][ cat ][ ][ dog ]
[3][ cat ][ bar ]
[4][ ][ hat ][ ][ ]
[5][ ][ cat ][ red ]
[6][ cat ]
[7][ ][ mad ][ ][ ]
[8][ cat ][ cat ][ ][ cat ]
If I use =countif(B1:B7,"cat") Google Sheets only counts cells B2, B5, and B8, and it doesn't count B1, B3, and B6.
Note that I didn't want to count cells A8 and D8, as I specifically only want to count duplicates in column B.
google-sheets google-sheets-formula
edited Nov 22 at 2:44
Rubén
9,99043265
asked Nov 20 at 18:56
DaMaxContent
1065
2
Yes nice table, but to answer your question: the issue is that there are no values in B1,B3 and B6. The value resides in Column A and as such a formula will not work here. This is the problem with using Merged Cells on anything but the final output. Formulas cannot determine if a cell is merged or not.
– Scott Craner
Nov 20 at 19:05
Is there a workaround without using apps script?
– DaMaxContent
Nov 20 at 19:10
1
No. We could count both A and B and subtract where both(see line 8), but what if there is no value in B and it is not merged, how is the formula supposed to know to skip that one. The only way is with script.
– Scott Craner
Nov 20 at 19:14
add a comment |
Given this sheet, I'd like to count duplicates in column B:
[ ][ A ][ B ][ C ][ D ]
[1][ cat ][ ][ ]
[2][ ][ cat ][ ][ dog ]
[3][ cat ][ bar ]
[4][ ][ hat ][ ][ ]
[5][ ][ cat ][ red ]
[6][ cat ]
[7][ ][ mad ][ ][ ]
[8][ cat ][ cat ][ ][ cat ]
If I use =countif(B1:B7,"cat") Google Sheets only counts cells B2, B5, and B8, and it doesn't count B1, B3, and B6.
Note that I didn't want to count cells A8 and D8, as I specifically only want to count duplicates in column B.
google-sheets google-sheets-formula
edited Nov 22 at 2:44
Rubén
9,99043265
asked Nov 20 at 18:56
DaMaxContent
1065
Given this sheet, I'd like to count duplicates in column B:
[ ][ A ][ B ][ C ][ D ]
[1][ cat ][ ][ ]
[2][ ][ cat ][ ][ dog ]
[3][ cat ][ bar ]
[4][ ][ hat ][ ][ ]
[5][ ][ cat ][ red ]
[6][ cat ]
[7][ ][ mad ][ ][ ]
[8][ cat ][ cat ][ ][ cat ]
If I use =countif(B1:B7,"cat") Google Sheets only counts cells B2, B5, and B8, and it doesn't count B1, B3, and B6.
Note that I didn't want to count cells A8 and D8, as I specifically only want to count duplicates in column B.
google-sheets google-sheets-formula
google-sheets google-sheets-formula
edited Nov 22 at 2:44
Rubén
9,99043265
asked Nov 20 at 18:56
DaMaxContent
1065
edited Nov 22 at 2:44
Rubén
9,99043265
asked Nov 20 at 18:56
DaMaxContent
1065
edited Nov 22 at 2:44
Rubén
9,99043265
edited Nov 22 at 2:44
Rubén
9,99043265
edited Nov 22 at 2:44
Rubén
9,99043265
9,99043265
asked Nov 20 at 18:56
DaMaxContent
1065
asked Nov 20 at 18:56
DaMaxContent
1065
asked Nov 20 at 18:56
DaMaxContent
1065
1065
2
Yes nice table, but to answer your question: the issue is that there are no values in B1,B3 and B6. The value resides in Column A and as such a formula will not work here. This is the problem with using Merged Cells on anything but the final output. Formulas cannot determine if a cell is merged or not.
– Scott Craner
Nov 20 at 19:05
Is there a workaround without using apps script?
– DaMaxContent
Nov 20 at 19:10
1
No. We could count both A and B and subtract where both(see line 8), but what if there is no value in B and it is not merged, how is the formula supposed to know to skip that one. The only way is with script.
– Scott Craner
Nov 20 at 19:14
add a comment |
2
Yes nice table, but to answer your question: the issue is that there are no values in B1,B3 and B6. The value resides in Column A and as such a formula will not work here. This is the problem with using Merged Cells on anything but the final output. Formulas cannot determine if a cell is merged or not.
– Scott Craner
Nov 20 at 19:05
Is there a workaround without using apps script?
– DaMaxContent
Nov 20 at 19:10
1
No. We could count both A and B and subtract where both(see line 8), but what if there is no value in B and it is not merged, how is the formula supposed to know to skip that one. The only way is with script.
– Scott Craner
Nov 20 at 19:14
2
2
Yes nice table, but to answer your question: the issue is that there are no values in B1,B3 and B6. The value resides in Column A and as such a formula will not work here. This is the problem with using Merged Cells on anything but the final output. Formulas cannot determine if a cell is merged or not.
– Scott Craner
Nov 20 at 19:05
Yes nice table, but to answer your question: the issue is that there are no values in B1,B3 and B6. The value resides in Column A and as such a formula will not work here. This is the problem with using Merged Cells on anything but the final output. Formulas cannot determine if a cell is merged or not.
– Scott Craner
Nov 20 at 19:05
Is there a workaround without using apps script?
– DaMaxContent
Nov 20 at 19:10
Is there a workaround without using apps script?
– DaMaxContent
Nov 20 at 19:10
1
1
No. We could count both A and B and subtract where both(see line 8), but what if there is no value in B and it is not merged, how is the formula supposed to know to skip that one. The only way is with script.
– Scott Craner
Nov 20 at 19:14
No. We could count both A and B and subtract where both(see line 8), but what if there is no value in B and it is not merged, how is the formula supposed to know to skip that one. The only way is with script.
– Scott Craner
Nov 20 at 19:14
add a comment |
1 Answer
1
active
oldest
votes
Excel considers that the value in a merged cell is actually contained in the first (most left) cell of the merged cells.
I can think only of one method to accomplish this with formulas, but it requires manual formatting of the merged cells, and a helper column.
You can format the merged cells as numbers with colored negative numbers. As there is text in the cells, this will have no impact on how the text is formatted, but you can still extract with CELL("color", A1) the formatting (1 if formatted to be red for negative numbers, and 0 otherwise).
In the helper column you copy the value from column B if the cell is not "colored", or from column A if "colored". Probably you also could accomplish this with an array formula and without using a helper column.
Anyway, it does require you to first (manually) format the merged cells (using format painter this obviously is very fast and easy if the number of rows is a reasonable number.
answered Nov 20 at 21:31
Peter K.
739112
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%2f53399743%2fcountif-with-merged-cells%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
Excel considers that the value in a merged cell is actually contained in the first (most left) cell of the merged cells.
I can think only of one method to accomplish this with formulas, but it requires manual formatting of the merged cells, and a helper column.
You can format the merged cells as numbers with colored negative numbers. As there is text in the cells, this will have no impact on how the text is formatted, but you can still extract with CELL("color", A1) the formatting (1 if formatted to be red for negative numbers, and 0 otherwise).
In the helper column you copy the value from column B if the cell is not "colored", or from column A if "colored". Probably you also could accomplish this with an array formula and without using a helper column.
Anyway, it does require you to first (manually) format the merged cells (using format painter this obviously is very fast and easy if the number of rows is a reasonable number.
answered Nov 20 at 21:31
Peter K.
739112
add a comment |
Excel considers that the value in a merged cell is actually contained in the first (most left) cell of the merged cells.
I can think only of one method to accomplish this with formulas, but it requires manual formatting of the merged cells, and a helper column.
You can format the merged cells as numbers with colored negative numbers. As there is text in the cells, this will have no impact on how the text is formatted, but you can still extract with CELL("color", A1) the formatting (1 if formatted to be red for negative numbers, and 0 otherwise).
In the helper column you copy the value from column B if the cell is not "colored", or from column A if "colored". Probably you also could accomplish this with an array formula and without using a helper column.
Anyway, it does require you to first (manually) format the merged cells (using format painter this obviously is very fast and easy if the number of rows is a reasonable number.
answered Nov 20 at 21:31
Peter K.
739112
add a comment |
Excel considers that the value in a merged cell is actually contained in the first (most left) cell of the merged cells.
I can think only of one method to accomplish this with formulas, but it requires manual formatting of the merged cells, and a helper column.
You can format the merged cells as numbers with colored negative numbers. As there is text in the cells, this will have no impact on how the text is formatted, but you can still extract with CELL("color", A1) the formatting (1 if formatted to be red for negative numbers, and 0 otherwise).
In the helper column you copy the value from column B if the cell is not "colored", or from column A if "colored". Probably you also could accomplish this with an array formula and without using a helper column.
Anyway, it does require you to first (manually) format the merged cells (using format painter this obviously is very fast and easy if the number of rows is a reasonable number.
answered Nov 20 at 21:31
Peter K.
739112
Excel considers that the value in a merged cell is actually contained in the first (most left) cell of the merged cells.
I can think only of one method to accomplish this with formulas, but it requires manual formatting of the merged cells, and a helper column.
You can format the merged cells as numbers with colored negative numbers. As there is text in the cells, this will have no impact on how the text is formatted, but you can still extract with CELL("color", A1) the formatting (1 if formatted to be red for negative numbers, and 0 otherwise).
In the helper column you copy the value from column B if the cell is not "colored", or from column A if "colored". Probably you also could accomplish this with an array formula and without using a helper column.
Anyway, it does require you to first (manually) format the merged cells (using format painter this obviously is very fast and easy if the number of rows is a reasonable number.
answered Nov 20 at 21:31
Peter K.
739112
answered Nov 20 at 21:31
Peter K.
739112
answered Nov 20 at 21:31
Peter K.
739112
answered Nov 20 at 21:31
Peter K.
739112
739112
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53399743%2fcountif-with-merged-cells%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
Yes nice table, but to answer your question: the issue is that there are no values in B1,B3 and B6. The value resides in Column A and as such a formula will not work here. This is the problem with using Merged Cells on anything but the final output. Formulas cannot determine if a cell is merged or not.
– Scott Craner
Nov 20 at 19:05
Is there a workaround without using apps script?
– DaMaxContent
Nov 20 at 19:10
1
No. We could count both A and B and subtract where both(see line 8), but what if there is no value in B and it is not merged, how is the formula supposed to know to skip that one. The only way is with script.
– Scott Craner
Nov 20 at 19:14