Hello Experts,

I need some assistance in creating an advanced formula that extracts the count of data from a spreadsheet using 2 criteria.

I tried this =(INDEX('9008 Data'!A5:A20,MATCH(1,IF('9008 Data'!B5:B20='Assessment Summary Report'!B18,IF('9008 Data'!D5:D20='Assessment Summary Report'!J16,1)),0))) but it only returned the first cell with the Pat id number and not the count. I tried this formula too: =INDEX('9008 Data'!A5:A20,MATCH('Assessment Summary Report'!J16&'Assessment Summary Report'!B18,'9008 Data'!F5:F20&'9008 Data'!D5:D20,0)) and got the same result.

My Data is below:

Pat Id Reference Category Added Date Month
1352 ORAL ONCOLOGY 1/21/2013 January
1446 RHEUMATOID ARTHRITIS 3/18/2013 March
1212 HEPATITIS C 2/27/2013 February
1351 MULTIPLE SCLEROSIS 3/13/2013 March
1774 HEPATITIS C 3/27/2013 March
1889 MULTIPLE SCLEROSIS 3/6/2013 March
2014 RHEUMATOID ARTHRITIS 3/4/2013 March
1000 HEMOPHILIA 2/21/2013 February
1202 HEPATITIS C 1/16/2013 January
1434 Rheumatoid Arthritis 3/28/2013 March
5007 MULTIPLE SCLEROSIS 3/5/2013 March
5003 MULTIPLE SCLEROSIS 2/25/2013 February
5008 HEPATITIS C 2/4/2013 February
1357 RHEUMATOID ARTHRITIS 1/16/2013 January
1448 RHEUMATOID ARTHRITIS 3/12/2013 March


I have also attached a copy of the file.

Using the Month and Reference Category (Program) as my criteria, I will like to know the count of the patients (using the pat ID) to determine the number of Patients offered therapy every month.

I also created a drop down menu in the Month cell so that when I select February for instance; the count of patients offered therapy for each program should populate in the respective cells.

Example: Month February

PROGRAM Patients offered Therapy
HEMOPHILIA 1
HEPATITIS C 2
MULTIPLE SCLEROSIS 1
ORAL ONCOLOGY
RHEUMATOID ARTHRITIS


Please help.

I hope to hear back soon. Thank you in advance for your anticipated assistance.

Addatone.

Hi Addatone,

Am Mon, 26 Aug 2013 23:10:28 +0100 schrieb Addatone:

I attached a copy of the file.

your attached file doesn't work. It gives an error when trying to open.

Try:
=COUNTIFS(Data!B2:B20,B18,Data!D2:D20,J16)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you Claus Busch!

I tested the formula and it works perfectly. Thank you so much for your help. I truly appreciate it.

Addatone.