Drop Down Menu linked to COUNTIF Function

We are a doctors office using excel to track outside physician referrals...

I have 13 sheets in my workbook, 1 for each month, and 1 yearly sheet which totals all referrals by month for each of the 2000 referring physician . Each monthly sheet is a report which lists all incoming referrals.

Monthly Sheets:
Column F: Type of referral
Column W: Physician's name.

Yearly Sheet:
G:R Monthly Totals
U: Physicians Name

On the yearly report, referrals are accumulated by:
=COUNTIF('Nov09'!$W$4:$W$1879,$U5)

I would like to have a drop down menu that lists types of referrals like:
X-Ray, CT, New, MRI, ALL

All should count all referrals, but each other selection should separate and only calculate those types of referrals.

I was thinking i need some sort of multiple criteria COUNTIF function linked to a drop down menu... Any help would be greatly appreciate!!!

Thank you

Can you upload the actual spreadsheet... it would help alot.

Perhaps something like:

=IF($X$1="ALL",COUNTA('Nov09'!$F$4:$F$1879),COUNTIF('Nov09'!$F$4:$F$1879,$X$1))

where X1 contains the dropdown list and 'Nov09'!$F$4:$F$1879 is the list of referrals.

How about,

=IF($X$1="ALL",COUNTIF('Nov09'!$W$4:$W$1879,$U5),SUMPRODUCT(--('Nov09'!$W$4:$W$1879=$U5),--('Nov09'!$F$4:$F$1879=$X$1))

where the parameters for the referrals are the same as I had mentioned before

Check that the matches are exact in the drop down list and in the source file..... spelling, extra spaces, etc... could affect the results.

It is not clear what column on the individual sheets I am supposed to look for matches to your drop down selection

Well since your are using XL2007 (please adjust your profile to match), then you can use, In G5:

=

Please Login or Register  to view this content.

copied down and adjusted for the other column/tab references.