# only if sumif counted text...

#### greggggg

##### New Member
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)

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try

=COUNTIFS(Visits!A2:A781200,A3)

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

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,"<>"&"")

Last edited:
wow worked.

you rule!

You're welcome.

Replies
15
Views
452
Replies
4
Views
469
Replies
1
Views
160
Replies
5
Views
104
Replies
3
Views
383

1,203,094
Messages
6,053,507
Members
444,667
Latest member
KWR21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back