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

"solinar" <solinar.22nz8d_1138988103.4091@excelforum-nospam.com> wrote in
message news:solinar.22nz8d_1138988103.4091@excelforum-nospam.com...
>
> 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.
>
>
> --
> solinar
> ------------------------------------------------------------------------
> solinar's Profile:
> http://www.excelforum.com/member.php...o&userid=31159
> View this thread: http://www.excelforum.com/showthread...hreadid=508216
>

[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

"solinar" <solinar.22o5xz_1138996803.8733@excelforum-nospam.com> wrote in
message news:solinar.22o5xz_1138996803.8733@excelforum-nospam.com...
>
> 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.
>
>
> --
> solinar
> ------------------------------------------------------------------------
> solinar's Profile:
> http://www.excelforum.com/member.php...o&userid=31159
> View this thread: http://www.excelforum.com/showthread...hreadid=508216
>

[Harlan Grove]

Biff wrote...
>>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.
....

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

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1139002027.791836.25540@g47g2000cwa.googlegroups.com...
> Biff wrote...
>>>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.
> ...
>
> 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)
>

[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!

[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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"solinar" <solinar.22nz8d_1138988103.4091@excelforum-nospam.com> wrote in
message news:solinar.22nz8d_1138988103.4091@excelforum-nospam.com...
>
> 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.
>
>
> --
> solinar
> ------------------------------------------------------------------------
> solinar's Profile:
> http://www.excelforum.com/member.php...o&userid=31159
> View this thread: http://www.excelforum.com/showthread...hreadid=508216
>