Count the number of times a combination of two values occurs in a cell
up vote
0
down vote
favorite
I want to count the number of time a combination of two values occurs in a cell. For example:
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,NCELLRNCID=1,NCELLID=2;
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,NCELLRNCID=1,NCELLID=4;
ADD UINTERFREQNCELL:RNCID=1,CELLID=1,NCELLRNCID=1,NCELLID=1;
What I'm trying to archive is:
=> count the number of times the combination of UINTRAFREQNCELL/UINTERFREQNCELL and the value of CELLID occurs:
So for the first two lines, the value will be 2 and for the third line the value will be 1.
How can I archive this?
microsoft-excel worksheet-function microsoft-excel-2016
asked Dec 5 at 9:37
aab
1032
|
show 11 more comments
up vote
0
down vote
favorite
I want to count the number of time a combination of two values occurs in a cell. For example:
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,NCELLRNCID=1,NCELLID=2;
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,NCELLRNCID=1,NCELLID=4;
ADD UINTERFREQNCELL:RNCID=1,CELLID=1,NCELLRNCID=1,NCELLID=1;
What I'm trying to archive is:
=> count the number of times the combination of UINTRAFREQNCELL/UINTERFREQNCELL and the value of CELLID occurs:
So for the first two lines, the value will be 2 and for the third line the value will be 1.
How can I archive this?
microsoft-excel worksheet-function microsoft-excel-2016
asked Dec 5 at 9:37
aab
1032
Could you share some sample data with us & the expected result will help us to fix the issue.
– Rajesh S
Dec 5 at 9:43
3 values you show is values of 3 separate cells in a column?
– Akina
Dec 5 at 9:47
@Akina yes they are three separate columns
– aab
Dec 5 at 9:56
And the value 2 for first two cells is calculated because they contain 'UINTRAFREQNCELL' substring and 'CELLID=3' substring? And the cell value has this format always, or there exists a lot of formats? in last case show some variants (maybe all if their count is small).
– Akina
Dec 5 at 10:00
@Akina Correct. All of the cells contain either 'UINTRAFREQNCELL' or 'UINTERFREQNCELL' and 'CELLID=x'.
– aab
Dec 5 at 10:06
|
show 11 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I want to count the number of time a combination of two values occurs in a cell. For example:
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,NCELLRNCID=1,NCELLID=2;
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,NCELLRNCID=1,NCELLID=4;
ADD UINTERFREQNCELL:RNCID=1,CELLID=1,NCELLRNCID=1,NCELLID=1;
What I'm trying to archive is:
=> count the number of times the combination of UINTRAFREQNCELL/UINTERFREQNCELL and the value of CELLID occurs:
So for the first two lines, the value will be 2 and for the third line the value will be 1.
How can I archive this?
microsoft-excel worksheet-function microsoft-excel-2016
asked Dec 5 at 9:37
aab
1032
I want to count the number of time a combination of two values occurs in a cell. For example:
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,NCELLRNCID=1,NCELLID=2;
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,NCELLRNCID=1,NCELLID=4;
ADD UINTERFREQNCELL:RNCID=1,CELLID=1,NCELLRNCID=1,NCELLID=1;
What I'm trying to archive is:
=> count the number of times the combination of UINTRAFREQNCELL/UINTERFREQNCELL and the value of CELLID occurs:
So for the first two lines, the value will be 2 and for the third line the value will be 1.
How can I archive this?
microsoft-excel worksheet-function microsoft-excel-2016
microsoft-excel worksheet-function microsoft-excel-2016
asked Dec 5 at 9:37
aab
1032
asked Dec 5 at 9:37
aab
1032
asked Dec 5 at 9:37
aab
1032
asked Dec 5 at 9:37
aab
1032
asked Dec 5 at 9:37
aab
1032
1032
Could you share some sample data with us & the expected result will help us to fix the issue.
– Rajesh S
Dec 5 at 9:43
3 values you show is values of 3 separate cells in a column?
– Akina
Dec 5 at 9:47
@Akina yes they are three separate columns
– aab
Dec 5 at 9:56
And the value 2 for first two cells is calculated because they contain 'UINTRAFREQNCELL' substring and 'CELLID=3' substring? And the cell value has this format always, or there exists a lot of formats? in last case show some variants (maybe all if their count is small).
– Akina
Dec 5 at 10:00
@Akina Correct. All of the cells contain either 'UINTRAFREQNCELL' or 'UINTERFREQNCELL' and 'CELLID=x'.
– aab
Dec 5 at 10:06
|
show 11 more comments
Could you share some sample data with us & the expected result will help us to fix the issue.
– Rajesh S
Dec 5 at 9:43
3 values you show is values of 3 separate cells in a column?
– Akina
Dec 5 at 9:47
@Akina yes they are three separate columns
– aab
Dec 5 at 9:56
And the value 2 for first two cells is calculated because they contain 'UINTRAFREQNCELL' substring and 'CELLID=3' substring? And the cell value has this format always, or there exists a lot of formats? in last case show some variants (maybe all if their count is small).
– Akina
Dec 5 at 10:00
@Akina Correct. All of the cells contain either 'UINTRAFREQNCELL' or 'UINTERFREQNCELL' and 'CELLID=x'.
– aab
Dec 5 at 10:06
Could you share some sample data with us & the expected result will help us to fix the issue.
– Rajesh S
Dec 5 at 9:43
Could you share some sample data with us & the expected result will help us to fix the issue.
– Rajesh S
Dec 5 at 9:43
3 values you show is values of 3 separate cells in a column?
– Akina
Dec 5 at 9:47
3 values you show is values of 3 separate cells in a column?
– Akina
Dec 5 at 9:47
@Akina yes they are three separate columns
– aab
Dec 5 at 9:56
@Akina yes they are three separate columns
– aab
Dec 5 at 9:56
And the value 2 for first two cells is calculated because they contain 'UINTRAFREQNCELL' substring and 'CELLID=3' substring? And the cell value has this format always, or there exists a lot of formats? in last case show some variants (maybe all if their count is small).
– Akina
Dec 5 at 10:00
And the value 2 for first two cells is calculated because they contain 'UINTRAFREQNCELL' substring and 'CELLID=3' substring? And the cell value has this format always, or there exists a lot of formats? in last case show some variants (maybe all if their count is small).
– Akina
Dec 5 at 10:00
@Akina Correct. All of the cells contain either 'UINTRAFREQNCELL' or 'UINTERFREQNCELL' and 'CELLID=x'.
– aab
Dec 5 at 10:06
@Akina Correct. All of the cells contain either 'UINTRAFREQNCELL' or 'UINTERFREQNCELL' and 'CELLID=x'.
– aab
Dec 5 at 10:06
|
show 11 more comments
1 Answer
1
active
oldest
votes
up vote
0
down vote
You can do this with COUNTIFS and wildcard characters:
=COUNTIFS(A:A,"*"&"UINTRAFREQNCELL"&"*",A:A,"*"&",CELLID="&"*")
=COUNTIFS(A:A,"*"&"UINTERFREQNCELL"&"*",A:A,"*"&",CELLID="&"*")
Here's a sample (I've bolded the relevant terms manually):
answered Dec 6 at 15:44
cybernetic.nomad
1,173111
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',
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%2f1380953%2fcount-the-number-of-times-a-combination-of-two-values-occurs-in-a-cell%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
up vote
0
down vote
You can do this with COUNTIFS and wildcard characters:
=COUNTIFS(A:A,"*"&"UINTRAFREQNCELL"&"*",A:A,"*"&",CELLID="&"*")
=COUNTIFS(A:A,"*"&"UINTERFREQNCELL"&"*",A:A,"*"&",CELLID="&"*")
Here's a sample (I've bolded the relevant terms manually):
answered Dec 6 at 15:44
cybernetic.nomad
1,173111
add a comment |
up vote
0
down vote
You can do this with COUNTIFS and wildcard characters:
=COUNTIFS(A:A,"*"&"UINTRAFREQNCELL"&"*",A:A,"*"&",CELLID="&"*")
=COUNTIFS(A:A,"*"&"UINTERFREQNCELL"&"*",A:A,"*"&",CELLID="&"*")
Here's a sample (I've bolded the relevant terms manually):
answered Dec 6 at 15:44
cybernetic.nomad
1,173111
add a comment |
up vote
0
down vote
up vote
0
down vote
You can do this with COUNTIFS and wildcard characters:
=COUNTIFS(A:A,"*"&"UINTRAFREQNCELL"&"*",A:A,"*"&",CELLID="&"*")
=COUNTIFS(A:A,"*"&"UINTERFREQNCELL"&"*",A:A,"*"&",CELLID="&"*")
Here's a sample (I've bolded the relevant terms manually):
answered Dec 6 at 15:44
cybernetic.nomad
1,173111
You can do this with COUNTIFS and wildcard characters:
=COUNTIFS(A:A,"*"&"UINTRAFREQNCELL"&"*",A:A,"*"&",CELLID="&"*")
=COUNTIFS(A:A,"*"&"UINTERFREQNCELL"&"*",A:A,"*"&",CELLID="&"*")
Here's a sample (I've bolded the relevant terms manually):
answered Dec 6 at 15:44
cybernetic.nomad
1,173111
answered Dec 6 at 15:44
cybernetic.nomad
1,173111
answered Dec 6 at 15:44
cybernetic.nomad
1,173111
answered Dec 6 at 15:44
cybernetic.nomad
1,173111
1,173111
add a comment |
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.
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%2fsuperuser.com%2fquestions%2f1380953%2fcount-the-number-of-times-a-combination-of-two-values-occurs-in-a-cell%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
Could you share some sample data with us & the expected result will help us to fix the issue.
– Rajesh S
Dec 5 at 9:43
3 values you show is values of 3 separate cells in a column?
– Akina
Dec 5 at 9:47
@Akina yes they are three separate columns
– aab
Dec 5 at 9:56
And the value 2 for first two cells is calculated because they contain 'UINTRAFREQNCELL' substring and 'CELLID=3' substring? And the cell value has this format always, or there exists a lot of formats? in last case show some variants (maybe all if their count is small).
– Akina
Dec 5 at 10:00
@Akina Correct. All of the cells contain either 'UINTRAFREQNCELL' or 'UINTERFREQNCELL' and 'CELLID=x'.
– aab
Dec 5 at 10:06