If Formulas

R

Reefaman

I am trying to compare the values in B4 down to B57 with the value i
E4. If any of the values in the B column match the value in E4 then
is put in K4. The only trouble is i am using the IF function and i
appears that it will only accept 6 IF's in any one formula, so i a
having to write the formulas as below:
=IF(E4=$B$4,1,IF(E4=$B$5,1,IF(E4=$B$6,1,IF(E4=$B$7,1,IF(E4=$B$8,1,IF(E4=$B$9,1,0)))))
thru to:
=IF(E4=$B$52,1,IF(E4=$B$53,1,IF(E4=$A$54,1,IF(E4=$A$55,1,IF(E4=$A$56,1,IF(E4=$A$57,1,0)))))
value placed in S4.
The total number of 1's in K4 to S4 are added and the result placed i
BD.
The problem is that if any of the values in B4 to B9 are duplicate
only one is accounted for in BD, but if the value in say B9 and B10 i
the same both values are accounted for and BD is 2.
Does anyone know how to overcome this problem as i dont want to coun
the duplicate values
 
B

bpeltzer

The way your formula begins, you're not counting the matches, but only
determining if there is any match (once one IF finds a match, you're setting
the result to 1). If you want the count, try =countif(b4:b57,e4). If you
just want to see if there is a match, =if(countif(b4:b57,e4)>0,1,0).
--Bruce
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top