only if sumif counted text...

[greggggg]

Hi,

I am using excel 2010.

sumif would work perfectly if I could count text in this case.

I have two worksheets, 'Utilization' and 'Visits'.

I have to match a unique identifier from the 'Utilization' sheet (in this case Unique ID 12345) with the Unique ID on the 'visits' sheet. I have been able to do this with match in the past. The unique identifier in this case represents a patient. More than one instance of a unique identifier in the 'visits' worksheet represents a different hospitalization or ER facility visit (for instance unique id 12345 has been to the ER 3 different times - once at MMMC, and twice at NYCMC. Once matched, I need count of the ER visits in the 'Utilization' sheet under column '#ER Visits'.

Worksheet 'Utilization'
Unique ID Demographic # ER Visits
12345 Bronx ???????

Worksheet 'Visits'
Unique ID ER facility visit
12345 MMMC
12345 NYCMC
12345 NYCMC

In this case '# of ER visits' for 12345 should equal 3.

I wanted to combine match with countif but have no idea how:

This is my countif
=COUNTIF(Visits!B2:B781200, Utilization!A3)

 

[gaz_chops]

Try

=COUNTIFS(Visits!A2:A781200,A3)

 

[greggggg]

Unfortunately that does not work , each instance of Unique ID cannot be used as a proxy for ER visits.

There are other columns in the 'visits' worksheet which will list outpatient visit facility and inpatient visit facility - so each instance of 'Unique ID' could be either a ER visit, an Outpatient visit, or an inpatient visit. In this case I need only ER visits.

I really appreciate the help though

 

[gaz_chops]

I'm guessing you can simply add "if column B is not blank". If not then post a sample that represents your data.

=COUNTIFS(VISITS!A2:A4,A3,VISITS!B2:B4,"<>"&"")

 

[greggggg]

wow worked.

you rule!

 

[gaz_chops]

You're welcome.