Need help with calculation

[srogersturner]

I'm having problems with one calculation and I'm wondering if anyone knows how to do this:

In a set of physician data I need to know if any of the physician's three offices fall into the territory of a specific hospital. Location of the office and the territory are defined by zip code.

1. In the worksheet of physician data I have one column the calculation
   2. I also have separate worksheets listing the zip codes defining the hospital's territory.
   3. In the hospital column I placed a COUNTIF function which tests to see if the zip code in Office 1 Zip is found in the list of the zip codes defining the hospital's territory. If it does, it returns a result of TRUE. It looks like this: =COUNTIF('Hospital Zips'!$A:$A,'Data Set'!V2)>=1. It works just fine
   4. However, the doc may have three office zip codes.
   5. Can this function be made to test if Office 1 Zip, OR, Office 2 Zip, OR, Office 3 Zip exists in the list of the zip codes defining the hospital's territory, then return a result of True?

Thanks,

Scott

 

[JE McGimpsey]

I'm having problems with one calculation and I'm wondering if anyone knows
how to do this:<br>
<br>
In a set of physician data I need to know if any of the physician's three
offices fall into the territory of a specific hospital. Location of the
office and the territory are defined by zip code.<br>
<br>
1. In the worksheet of physician data I have one column the calculation<br>
&nbsp;&nbsp;&nbsp;2. I also have separate worksheets listing the zip codes
defining the hospital's territory.<br>
&nbsp;&nbsp;&nbsp;3. In the hospital column I placed a COUNTIF function which
tests to see if the zip code in Office 1 Zip is found in the list of the zip
codes defining the hospital's territory. If it does, it returns a result of
TRUE. It looks like this: =COUNTIF('Hospital Zips'!$A:$A,'Data
Set'!V2)&gt;=1. It works just fine<br>
&nbsp;&nbsp;&nbsp;4. However, the doc may have three office zip codes.<br>
&nbsp;&nbsp;&nbsp;5. Can this function be made to test if Office 1 Zip, OR,
Office 2 Zip, OR, Office 3 Zip exists in the list of the zip codes defining
the hospital's territory, then return a result of True?<br>

How are you specifying the test zip codes?

If V2,V3,V4, etc...

=(COUNTIF('Hospital Zips'!$A:$A,'Data Set'!V2) + COUNTIF('Hospital
Zips'!$A:$A,'Data Set'!V3) + COUNTIF('Hospital Zips'!$A:$A,'Data
Set'!V4)) >= 1

or, array entered (CMD-ENTER):

=SUM(COUNTIF('Hospital Zips'!$A:$A,V2:V4)) > 0

You can, if you specify literals (constants):

=COUNTIF('Hospital Zips'!$A:$A,{12345,12346,12348})

Another way: