Using Lookup function in a larger formula

Hello,
I am currently using the following formula:
=SUMPRODUCT(('Step 1_Data_Current Qtr'!T2:T10000=B2)*('Step 1_Data_Current Qtr'!C2:C10000<>"")*1)

...to count the number of instances in a data set where:

T2:T10000=B2 is counting records where the value in the cell is set to B2

and

C2:C10000<>"" is counting records where the value in the cell is not blank.

The current formular is working, but I want to change the formula so that the values in C2:C10000 is looking up specific names (in this case, physicians) and not just where the field is not blank. How can I change to lookup physicians in a specific list that may change, located in a separate tab from the main data set? Thank you.

If you assume your physician list is on a sheet called Physicians List in range A1:A10 you could possibly use something like:

Please Login or Register  to view this content.

However note that this would count 2 instances of Physician "X" as 2 not 1... are you wanting a distint count of physicians... ie if Physician X appears twice in C2:C10000 where T2:T10000=B2 do you want to count that particular physician once or twice ?

I would like to count the number of times the physician comes up in the field for the value set in B2. Does that help? Thanks. What do the dashes (--) mean in the formula? Thanks.

Sorry not really -- if B2 holds physician why are you checking col T against that value ? I thought the physician was to be checked against col C ?

EDIT: I think we're talking at x-purposes.

Put your physician list in col A on a new sheet called Physicians List then test the formula provided altering the physician list range to match your own lists dimensions.

Try my suggestion.

Add a sheet -- call it Physicians List. Add names you want to look for in A1 onwards of this sheet.

Adjust the formula provided such that this:

Please Login or Register  to view this content.

matches your real range... ie if you have 100 listed use A1:A100.

Post back once you've tested this approach.

first let's change the SEARCH to a MATCH:

Please Login or Register  to view this content.

If the number is still incorrect can you elaborate on whether the following:

"N" / "Mr X"
"N" / "Mr X"

should be counted once or twice
(assuming B2 = "N" and "MrX" is listed on your Physician List)

Sorry, I was not around to answer your question immediately.

Without the ISNUMBER the MATCH will either return an Index position (number) of the match in the lookup range if it can find one or an #N/A error value if no MATCH can be found.

On that basis you can use ISNUMBER(MATCH(...)) to return TRUE for a Match (as a MATCH would return the INDEX position of that match value within the lookup range - ie a number) or FALSE for no match as it would return an error value (not number).
(if you used ISNA instead of ISNUMBER you would get the opposite result -- an Error would return TRUE and a Match would return FALSE)

The double unary operator (--) then coerces the boolean TRUE/FALSE values to 1/0 respectively.