CODEWITHSUNDEEP
excelvbacountiffind-occurrences
Bonnie Z
Bonnie Z

Reputation: 55

VBA: Find number of occurrences in one column based on number of occurrences in a second column

I need to figured out a way to count the number of times a number appears in Column B based on it matching a value in column A.

Example:

Column A       Column B    
2172223333    2172245555    
2172223333    2172245555    
2172234444    2172245555    
2172234444    2172245555    
2172285544    2172245555

So in this example I would need to calculate that

2172245555 occurs 2 times in relation to 2172223333

2172245555 occurs 2 times in relation to 2172234444

2172245555 occurs 1 time in relation to 2172285544

Any suggestions?

Upvotes: 0

Views: 927

Answers (2)

ARickman
ARickman

Reputation: 601

You can also use a helper column that concatenates Columns A and B. This creates a Composite Key which you can then use in the CountIf function like so:

enter image description here

  • Column C Formula: =A2&B2
  • Column D Formula: =COUNTIF($C$2:$C6,$C2)
  • Column E Formula: =COUNTIF($C$2:$C2,$C2)

Note: I added the running count Column (Column E) for kicks, so its not necessary, just another option you might want to pull out in the future.

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

Just use the following formula in column C

=COUNTIFS(A:A,A:A,B:B,B:B)

to get the occurences of the combinations.

enter image description here

Alternatively you can use in cell C2

=COUNTIFS(A:A,A2,B:B,B2)

… and copy it down.

Edit to proof my comment below: enter image description here see the last line here. 2172223333 occurs but the combination counts only the occurences of combinations of column A and B.

Upvotes: 1

Related Questions