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
Last edited by mygsprc; 11-08-2010 at 11:34 AM.
Can you upload the actual spreadsheet... it would help alot.
Last edited by NBVC; 11-05-2010 at 01:57 PM. Reason: Deleted entire post quote
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
KRBeckman, I cant post the workbook because it contains patient sensitive material.
NBVC, That is very close, but I need to have it also filtered by the physicians name which is in column U. It is currently giving me all referrals of that type for that month.
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
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hmm.. you are on the right track but something is wrong.. It calculates to 0 when I should be getting a 16.
Check that the matches are exact in the drop down list and in the source file..... spelling, extra spaces, etc... could affect the results.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thank you for your help last week, but I was not able to figure it out over the weekend. I went ahead and deleted all of the patient sensitive information so I could upload the spreadsheet for you.
To restate my objective:
S1 contains the dropdown list.
I would like to replace the formulas in Year!G5:Q1965 to incorporate a filter function relative to S1.
Any help would be great. Thank you.
It is not clear what column on the individual sheets I am supposed to look for matches to your drop down selection
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Oh Sorry,
The drop down list contains certain VisitTypes which are found in column D of the other sheets.
Well since your are using XL2007 (please adjust your profile to match), then you can use, In G5:
=copied down and adjusted for the other column/tab references.IF($R$1="All",COUNTIF('Dec09'!$E$4:$E$2000,$T5&$U5)+COUNTIF('Dec09'!$E$4:$E$2000,$U5&$T5),COUNTIFS('Dec09'!$E$4:$E$2000,$T5&$U5,'Dec09'!$D$4:$D$2000,Year!$R$1)+COUNTIFS('Dec09'!$E$4:$E$2000,$U5&$T5,'Dec09'!$D$4:$D$2000,Year!$R$1))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
worked perfect. thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks