How can you use multiple ranges in a function?

[solinar]

I am looking for a function to search through multiple ranges of cells
and return a number if it finds any instances of that number.

For instance:
I have say 6 cells, (A1, C17, F13:G14) and I want to find out if any of
them contain the number "1" and if they do, I want the result of B2 to
be "1".

The way I thought I could accomplish this was to go to cell B2 and do
the formula =if(countif((A1,C17,F13:G14),"1")>0,1,0)

This seems to work for a single range such as
=if(countif(F13:G14,"1")>0,1,0)

I just dont know the proper syntax for including multiple ranges in a
function.

If anyone knows how to include multiple ranges in a function (if its
possible), or knows a simpler way to do what I am trying to do, the
help would be greatly appreciated.

 

[Biff]

Hi!

Try this:

=IF(OR(A1=1,C17=1,COUNTIF(F13:G14,1)),1,0)

Biff

 

[Bernard Liengme]

=IF(OR(A1=1, C17=1, count(F13:G14,1)>0), 1, "what to do otherwise")
=--(A1=1)+--(C17=1)+--(COUNTIF(F13:G14,1)>1) will return 1 or 0

Why did you put quotes around the number 1 - quotes generally needed only
with text
best wishes

 

[solinar]

Hi:

Thanks for the response, but I tried the countif function because using
if and or functions were too cumbersome. My actual problem has about 10
different ranges to search and I need to perform 27 different instances
of this.

I was hoping to find something easier than typing a logic statement for
each of the 10 ranges (and then modifying that for each of the 27
instances I have to do it in).

Thanks anyway for the reply though.

 

[Biff]

My actual problem has about 10

different ranges to search and I need to
perform 27 different instances of this.

Then why didn't you "say" that in the first place? <g>

There is no elegant way to do this! You're going to end up with a long ugly
formula. The OR function can have up to 30 arguments so you're well within
that limit with just 10.

Biff

 

[Harlan Grove]

Biff wrote...

Then why didn't you "say" that in the first place? <g>

There is no elegant way to do this! You're going to end up with a long ugly
formula. The OR function can have up to 30 arguments so you're well within
that limit with just 10.

....

There isn't?!

If all the ranges involved were in a single worksheet, consider

=IF(INDEX(FREQUENCY((rng1,rng2,rng3,rng4),x+10^INT(LOG(x)-14)*{-1;0;1}),2),1,0)

And multiple area ranges aren't subject to a limit of 30 or fewer
areas. They're only subject to the limit on formula length, and that
could be extended using defined names. Consider

=IF(INDEX(FREQUENCY((B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,
B3,C3,D3,E3,F3,G3,H3,I3,J3,K3,L3,M3,B4,C4,D4,E4,F4,G4,H4,I4,J4,K4,L4,M4,
B5,C5,D5,E5,F5,G5,H5,I5,J5,K5,L5,M5),x+10^INT(LOG(x)-14)*{-1;0;1}),2),1,0)

 

[Biff]

There is no elegant way to do this!

There isn't?!

I guess it depends on ones definition of elegant!

Biff

 

[solinar]

Excellent. It seems the frequency function has no problem with dealing
with multiple ranges separated by commas within quotes. This did the
trick.

Thanks all for the help!