How to count cells if it contains a value in Excel
I have an Excel sheet like this:
ID | Relations
----+----------------
1 | ,
2 | ,
3 | ,1,
4 | ,1,2,
5 | ,2,
6 | ,3,
7 | ,1,2,4,
8 | ,1,2,4,5,6,
9 | ,2,4,5,1,I want to count Relations as Related Count column - that checks if finding ,ID, in Relations is true - with a formula to achieve a result like this:
ID | Relations | Related Count
----+---------------+----------------
1 | , | 5 '>> related in: 3,4,7,8,9
2 | , | 5 '>> related in: 4,5,7,8,9
3 | ,1, | 1 '>> related in: 6
4 | ,1,2, | 3 '>> related in: 7,8,9
5 | ,2, | 2 '>> related in: 8,9
6 | ,3, | 1 '>> related in: 8
7 | ,1,2,4, | 0
8 | ,1,2,4,5,6, | 0
9 | ,2,4,5,1, | 0Edit:
I know how to use countif() function, Please help me in finding a formula for Related Count column.
Thanks in advance.
3 Answers
COUNTIF does an exact match of a value against the entire cell, it doesn't act like a "contains" function. However, you can use wildcards as a workaround, because wildcards represent everything else within the cell. So you can accomplish what you want with COUNTIF, and an array formula isn't required.
The formula in C2:
=COUNTIF($B$2:$B$10,"*,"&A2&",*")This surrounds the A2 value with commas, so it isn't limited to single digits. Then the asterisks represent anything else before or after.
This answer will work if you've only got 9 IDs:
=SUM(IF(ISNUMBER(FIND(A2,B:B)),1,0))This works by looking for A2 within the contents of every cell in B - this differs from COUNTIF as that looks for the entire contents matching. The IF and ISNUMBER then returns a 1 for each cell in which the number is found and a 0 otherwise. It then uses SUM to add the 1s and 0s.
This is an array formula so after you've entered it into your worksheet you'll need to press Ctrl, Alt + Enter.
Screenshot showing this working:
If you have IDs and Relations that go to 10, 11 etc. then it won't work as it will find the 1 as the first digit in 10 and affect your results for 1
2You should use =COUNTIF(Range to count; Parameter to find) in your case let's say your dataset starts in A1
=COUNTIF($B$2:$B10; $A1) and shoud be input in column C
Cheers
5
