Count appearances of two-adjacent-cell value in range (Excel)

Clash Royale CLAN TAG#URR8PPP

up vote
1
down vote

favorite

I am working on a sports analytics tracker in Excel, and for one part I need to count (I know there are other questions that just need to find them) how many appearances there are of a two-cell value in a certain range. So, for example, if I was searching for:

-----------------
| foo | bar |
-----------------

in:

-----------------
| foo | bar |
-----------------
| far | foo |
-----------------
| far | fob |
-----------------
| foo | bar |
-----------------
| boo | bar |
-----------------
| far | foo |
-----------------
| foo | bar |
-----------------

I would like the result to be 3, as the query appears three times. I would like to use a formula for this: I have tried using COUNTIF, and while I was able to count up how many matched in each column, I couldn't see how many matched the two-cell combination.

Any help would be appreciated.

microsoft-excel worksheet-function microsoft-excel-2016 countif

share|improve this question

asked 4 hours ago

Geza Kerecsenyi

153

New contributor

Geza Kerecsenyi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

up vote
1
down vote

favorite

I am working on a sports analytics tracker in Excel, and for one part I need to count (I know there are other questions that just need to find them) how many appearances there are of a two-cell value in a certain range. So, for example, if I was searching for:

-----------------
| foo | bar |
-----------------

in:

-----------------
| foo | bar |
-----------------
| far | foo |
-----------------
| far | fob |
-----------------
| foo | bar |
-----------------
| boo | bar |
-----------------
| far | foo |
-----------------
| foo | bar |
-----------------

I would like the result to be 3, as the query appears three times. I would like to use a formula for this: I have tried using COUNTIF, and while I was able to count up how many matched in each column, I couldn't see how many matched the two-cell combination.

Any help would be appreciated.

microsoft-excel worksheet-function microsoft-excel-2016 countif

share|improve this question

asked 4 hours ago

Geza Kerecsenyi

153

New contributor

Geza Kerecsenyi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

up vote
1
down vote

favorite

up vote
1
down vote

favorite

I am working on a sports analytics tracker in Excel, and for one part I need to count (I know there are other questions that just need to find them) how many appearances there are of a two-cell value in a certain range. So, for example, if I was searching for:

-----------------
| foo | bar |
-----------------

in:

-----------------
| foo | bar |
-----------------
| far | foo |
-----------------
| far | fob |
-----------------
| foo | bar |
-----------------
| boo | bar |
-----------------
| far | foo |
-----------------
| foo | bar |
-----------------

I would like the result to be 3, as the query appears three times. I would like to use a formula for this: I have tried using COUNTIF, and while I was able to count up how many matched in each column, I couldn't see how many matched the two-cell combination.

Any help would be appreciated.

microsoft-excel worksheet-function microsoft-excel-2016 countif

share|improve this question

asked 4 hours ago

Geza Kerecsenyi

153

New contributor

Geza Kerecsenyi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

I am working on a sports analytics tracker in Excel, and for one part I need to count (I know there are other questions that just need to find them) how many appearances there are of a two-cell value in a certain range. So, for example, if I was searching for:

-----------------
| foo | bar |
-----------------

in:

-----------------
| foo | bar |
-----------------
| far | foo |
-----------------
| far | fob |
-----------------
| foo | bar |
-----------------
| boo | bar |
-----------------
| far | foo |
-----------------
| foo | bar |
-----------------

I would like the result to be 3, as the query appears three times. I would like to use a formula for this: I have tried using COUNTIF, and while I was able to count up how many matched in each column, I couldn't see how many matched the two-cell combination.

Any help would be appreciated.

microsoft-excel worksheet-function microsoft-excel-2016 countif

microsoft-excel worksheet-function microsoft-excel-2016 countif

share|improve this question

asked 4 hours ago

Geza Kerecsenyi

153

New contributor

Geza Kerecsenyi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 4 hours ago

Geza Kerecsenyi

153

New contributor

Geza Kerecsenyi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

share|improve this question

asked 4 hours ago

Geza Kerecsenyi

153

New contributor

Geza Kerecsenyi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 hours ago

Geza Kerecsenyi

153

asked 4 hours ago

Geza Kerecsenyi

153

153

New contributor

Geza Kerecsenyi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

New contributor

Geza Kerecsenyi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

Geza Kerecsenyi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

add a comment | 

2 Answers
2

active

oldest

votes

up vote
1
down vote



accepted

Consider:

=SUMPRODUCT(--(A:A="foo")*(B:B="bar"))

share|improve this answer

answered 4 hours ago

Gary's Student

13k31629

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Works perfectly - thanks!
  – Geza Kerecsenyi
  4 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @GezaKerecsenyi Thanks for the feedback!
  – Gary's Student
  4 hours ago
  

  
  
  
  

add a comment | 

up vote
1
down vote

This is what COUNTIFS is for:

=COUNTIFS(A:A,"foo",B:B,"bar")

share|improve this answer

answered 4 hours ago

Scott Craner

10.1k1814

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: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

Geza Kerecsenyi is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

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

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1362476%2fcount-appearances-of-two-adjacent-cell-value-in-range-excel%23new-answer', 'question_page');

);

Post as a guest

Name Email

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
1
down vote



accepted

Consider:

=SUMPRODUCT(--(A:A="foo")*(B:B="bar"))

share|improve this answer

answered 4 hours ago

Gary's Student

13k31629

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Works perfectly - thanks!
  – Geza Kerecsenyi
  4 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @GezaKerecsenyi Thanks for the feedback!
  – Gary's Student
  4 hours ago
  

  
  
  
  

add a comment | 

up vote
1
down vote



accepted

Consider:

=SUMPRODUCT(--(A:A="foo")*(B:B="bar"))

share|improve this answer

answered 4 hours ago

Gary's Student

13k31629

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Works perfectly - thanks!
  – Geza Kerecsenyi
  4 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @GezaKerecsenyi Thanks for the feedback!
  – Gary's Student
  4 hours ago
  

  
  
  
  

add a comment | 

up vote
1
down vote



accepted

up vote
1
down vote



accepted

Consider:

=SUMPRODUCT(--(A:A="foo")*(B:B="bar"))

share|improve this answer

answered 4 hours ago

Gary's Student

13k31629

Consider:

=SUMPRODUCT(--(A:A="foo")*(B:B="bar"))

share|improve this answer

answered 4 hours ago

Gary's Student

13k31629

share|improve this answer

share|improve this answer

answered 4 hours ago

Gary's Student

13k31629

answered 4 hours ago

Gary's Student

13k31629

answered 4 hours ago

Gary's Student

13k31629

13k31629

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Works perfectly - thanks!
  – Geza Kerecsenyi
  4 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @GezaKerecsenyi Thanks for the feedback!
  – Gary's Student
  4 hours ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Works perfectly - thanks!
  – Geza Kerecsenyi
  4 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @GezaKerecsenyi Thanks for the feedback!
  – Gary's Student
  4 hours ago
  

  
  
  
  

Works perfectly - thanks!
– Geza Kerecsenyi
4 hours ago

Works perfectly - thanks!
– Geza Kerecsenyi
4 hours ago

@GezaKerecsenyi Thanks for the feedback!
– Gary's Student
4 hours ago

@GezaKerecsenyi Thanks for the feedback!
– Gary's Student
4 hours ago

add a comment | 

up vote
1
down vote

This is what COUNTIFS is for:

=COUNTIFS(A:A,"foo",B:B,"bar")

share|improve this answer

answered 4 hours ago

Scott Craner

10.1k1814

add a comment | 

up vote
1
down vote

This is what COUNTIFS is for:

=COUNTIFS(A:A,"foo",B:B,"bar")

share|improve this answer

answered 4 hours ago

Scott Craner

10.1k1814

add a comment | 

up vote
1
down vote

up vote
1
down vote

This is what COUNTIFS is for:

=COUNTIFS(A:A,"foo",B:B,"bar")

share|improve this answer

answered 4 hours ago

Scott Craner

10.1k1814

This is what COUNTIFS is for:

=COUNTIFS(A:A,"foo",B:B,"bar")

share|improve this answer

answered 4 hours ago

Scott Craner

10.1k1814

share|improve this answer

share|improve this answer

answered 4 hours ago

Scott Craner

10.1k1814

answered 4 hours ago

Scott Craner

10.1k1814

answered 4 hours ago

Scott Craner

10.1k1814

10.1k1814

add a comment | 

add a comment | 

Geza Kerecsenyi is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

Geza Kerecsenyi is a new contributor. Be nice, and check out our Code of Conduct.

Geza Kerecsenyi is a new contributor. Be nice, and check out our Code of Conduct.

Geza Kerecsenyi is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

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

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1362476%2fcount-appearances-of-two-adjacent-cell-value-in-range-excel%23new-answer', 'question_page');

);

Post as a guest

Name Email

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

Name Email

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

Name Email

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

Name Email

Name Email

Name

Email

Name Email

Name

Email