I made a function to search a sheet to match a cell on another sheet.

=IF(COUNTIF(M16M4M203!$O3:$O24,B3)0,"X","")

This works great, but I need it to only search certain cells on the sheet. I
tried to just select those cells and control-click another group but Excel
tells me "You've entered too many arguements for this function"

Do I need to make a function for each range of cells to search? If so, how
do I put more than one function in a single cell.

Thank you

You have two If clauses but only one logical test -- that's why the formula
is flawed.
Also, individual cells need to be seperated by a comma.

=if(COUNT OF RANGE 0, "X","")
if that's correct =if(Count(M16,M15,$03:$024,B3)0,"X","")
will count the individual cells and $03:$o24 with numbers in it. If any of
them have a number it will mark the cell with the formula as X. I've taken
the CountIf test out because I couldn't figure what what if was supposed to
be compared to.

Did I get it correct?
Ian

"Spamn" wrote:

I made a function to search a sheet to match a cell on another sheet.

=IF(COUNTIF(M16M4M203!$O3:$O24,B3)0,"X","")

This works great, but I need it to only search certain cells on the sheet. I
tried to just select those cells and control-click another group but Excel
tells me "You've entered too many arguements for this function"

Do I need to make a function for each range of cells to search? If so, how
do I put more than one function in a single cell.

Thank you

I'm not sure.. I understand why you took the other IF out, it works either
way. Less work that way though.

If you look at my example below you'll see that I'm searching a sheet named
"M16M4M203" between cells 03-024. My question is, what if I wanted to search
cells in say the upper right hand corner of my sheet and nothing else around
it. How would I do that? I've tried highlighting the cells to be searched and
it gives me this function

=IF(COUNT(M16M4M203!M3:Q29,B3)0,"X","")

but that searches more than just that section.

I hope this helps explain my problem a little more.



"Ian" wrote:

You have two If clauses but only one logical test -- that's why the formula
is flawed.
Also, individual cells need to be seperated by a comma.

=if(COUNT OF RANGE 0, "X","")
if that's correct =if(Count(M16,M15,$03:$024,B3)0,"X","")
will count the individual cells and $03:$o24 with numbers in it. If any of
them have a number it will mark the cell with the formula as X. I've taken
the CountIf test out because I couldn't figure what what if was supposed to
be compared to.

Did I get it correct?
Ian

"Spamn" wrote:

I made a function to search a sheet to match a cell on another sheet.

=IF(COUNTIF(M16M4M203!$O3:$O24,B3)0,"X","")

This works great, but I need it to only search certain cells on the sheet. I
tried to just select those cells and control-click another group but Excel
tells me "You've entered too many arguements for this function"

Do I need to make a function for each range of cells to search? If so, how
do I put more than one function in a single cell.

Thank you

Do I need to make a function for each range of cells to search?

Maybe. How many different ranges are there? Are they all the same size?

One way to do it provided all the ranges are the same size and shape:

=IF(SUMPRODUCT(--(A1:A5=B3)+(K16:K20=B3)),"X",0)

Or, if the ranges differ in size/shape, you can always do this:

=IF(COUNTIF(A1:A15,B3)+COUNTIF(K16:AA16,B3),"X","" )

Biff

"Spamn" wrote in message
...
I made a function to search a sheet to match a cell on another sheet.

=IF(COUNTIF(M16M4M203!$O3:$O24,B3)0,"X","")

This works great, but I need it to only search certain cells on the sheet.
I
tried to just select those cells and control-click another group but Excel
tells me "You've entered too many arguements for this function"

Do I need to make a function for each range of cells to search? If so, how
do I put more than one function in a single cell.

Thank you