CODEWITHSUNDEEP
excel
WildBill
WildBill

Reputation: 9291

How to count pairings of cells in spreadsheet

Let's say I have a spreadsheet that has two columns of data. For example:

A    B
1    2
2    3
1    1
1    2
1    3
2    3
2    1

How could I count the total number of times each pair appears in the spreadsheet. In the example above, 1-2 should have a count of 3 (1 for 2-1, 2 for 1-2), 2-3 should have a count of 2etc

Upvotes: 4

Views: 5943

Answers (2)

JMax
JMax

Reputation: 26591

Note that for Excel versions >2007, you can use the new formula COUNTIFS:

[EDIT] Added the correct solution (credits to Chris Nielsen - see the comments)

=COUNTIFS($A$1:$A$12,A1,$B$1:$B$12,B1) + COUNTIFS($A$1:$A$12,B1,$B$1:$B$12,A1)

Upvotes: 6

Jesse
Jesse

Reputation: 1935

One way would be to use a SUMPROUDUCT in column C. That would give you a count of the number of times the combination in that row is encountered.

=SUMPRODUCT(--($A$1:$A$7=A1),--($B$1:$B$7=B1))+SUMPRODUCT(--($A$1:$A$7=B1),--($B$1:$B$7=A1))

Following your example this would output:

A   B   C
1   2   3
2   3   2
1   1   1
1   2   3
1   3   1
2   3   2
2   1   3

SUMPRODUCT can be tricky, you can read some good howtos here:

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

Jesse

Upvotes: 3

Related Questions