1

In Excel, how can I count how many times one value appears in the same row as another value. for example in this array, how many times does 5 appear with 6. I can see its 3 times but need a formula to count it for me

5 6 12 13 22 35
1 10 25 33 35 2
4 9 10 20 33 2
1 7 10 16 24 1
1 5 6 18 30 22
9 11 14 28 33 5
1 6 15 25 28 5
7 10 17 22 34 7
  • You mean entire, delimited numbers? So in your example, 52 and 36 wouldn't count? – fixer1234 Dec 25 '14 at 21:21
  • no another example. how many times 12 and 13 appear on the same line – Doodman Dec 25 '14 at 21:43
  • Do all lines always contain non-duplicate numbers, so in your example, if a line contains 5 and 6 there can be only one five and only one 6? Will a specific target pair be defined or is this a general case where you want to identify all cases where any two values appear on multiple lines? – fixer1234 Dec 25 '14 at 22:01
  • All lines always contain non duplicate numbers. I want to find how many times any number appears on the same line with any other number over all the data, I've just realized that some numbers are duplicated on the same line on the example , I just copied and pasted it. – Doodman Dec 25 '14 at 22:12
  • No row would have a duplicate number, cheers – Doodman Dec 25 '14 at 22:14
1

Here is a straightforward way to do it. There may be a more elegant method using an array formula but this works. Say your data starts in A1, and the two target values are in I1 and J1. Use column H as a helper column to test each row. So H1 would be:

=COUNTIF(A1:F1,I$1)*COUNTIF(A1:F1,J$1)

This relies on your assumption that there are no duplicates of a value in a row. There are many ways to combine the double test. What I did here was to multiply the two counts. Each count can be only 0 or 1, so the multiplication produces a 1 only if both values are there. Copy this formula down column H for all of your data rows.

For the count you want, put this formula in your result cell:

=SUM(H1:H8)

Of course, adjust the range for the actual number of rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.