Sumproduct-select a range

**hanymon** · 03-09-2007, 09:50 PM

Hi guys, I'm trying to select a range of cells to test a text value using if condition but for soem reason it is not working! for example if i use :
=IF (OR(a1="mike",b10="mike",c4="mike"),1,"") this will work fine but is there any other way to write this formula so i do not have to write every single cell and just chose them.

thanx in advance.

**mudraker** · 03-09-2007, 09:55 PM

hanymon

Please change Title of your thread or you may find your post deleted

For More Info see
http://www.excelforum.com/showthread.php?t=591596

**Paul** · 03-09-2007, 09:57 PM

If you're trying to count the number of occurrences of "Mike" in a range, use this formula:

=SUMPRODUCT(--(A1:E10="mike"))

If you're just trying to see if "Mike" exists anywhere in the range, and if so, put a 1 in the cell with the formula, use this:

=IF(SUMPRODUCT(--(A1:E10="mike"))>0,1,"")

Adjust your range as needed, A1:E10 is just an example.

**hanymon** · 03-09-2007, 10:14 PM

Thank you, PJoaquin, your second answer did the job, and i do appologize if i did post this way it is my first time on this site and i did not know what are the rules, but thanks again for the help

**davesexcel** · 03-09-2007, 10:39 PM

So change the title,

If somebody was searching the forum for "if or" formula, they would never think of searching for
"anybody has an idea?"

The majority of searches find the titles