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
Improve this question
7
  • You mean entire, delimited numbers? So in your example, 52 and 36 wouldn't count?
    – fixer1234
    Dec 25, 2014 at 21:21
  • no another example. how many times 12 and 13 appear on the same line
    – Doodman
    Dec 25, 2014 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, 2014 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, 2014 at 22:12
  • No row would have a duplicate number, cheers
    – Doodman
    Dec 25, 2014 at 22:14

1 Answer 1

Reset to default
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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .