CountIf in Filtered Table

**cityofnr** · 01-05-2011, 03:24 PM

I have reviewed other questions regarding this subject, but didn't see anything that helped pertaining to my situation.

I have a table in which I will filter ambulance runs by month. The table has various data about the specific call and the amount charged.

I also have 3 columns to specify which EMT went on the run. At the end of each month I'd like to have a tally of how many runs each EMT went on.

It's set up like such( there are other columns in between amount and balance that I didn't put in this scenario:

Date Run # Name Amount Balance EMT1 EMT2 EMT3
1/1/11 2 Doe, Jane $200 $200 Sue John Bill
2/1/11 3 Deer, Joe $200 $0 Ron Sue John

So at the end of January I can filter for just the January runs, and on the right of the table I have a list of EMT's. I'd like to have beside them a calcualtion of the number of runs they took in that month. Like:

EMT #of Runs
Sue (would like calculated based on the 3 columns in table)
John
Ron
Bill

Obviously the Countif function counts all the runs regardless of the filter. If there is a way to set this up simpler that I'm not seeing, I'm open to those suggestions.

Thanks in advance for your help.

**DonkeyOte** · 01-05-2011, 03:39 PM

First off I presume if you're filtering the data your results table is not to the right of the main table else you'd probably find yourself hiding results as you filter.

Your profile implies XL2007 - is that correct ?

If so, are you using TABLEs or is your data stored in a standard range

A quick fix if you're using XL2007

We'll assume for sake of demo. that the above resides on sheet called DATA and that results are on a sheet called RESULTS

Adjacent to EM3 column on DATA sheet add the following in consecutive columns:

Please Login or Register  to view this content.

Again for sake of demo let's assume the above resides in Column Z on DATA sheet... then for results:

RESULTS

Please Login or Register  to view this content.

if you are using Tables you can replace the range reference as appropriate

**cityofnr** · 01-05-2011, 03:57 PM

Thank you for your quick reply. The data was to the right, but I moved it up so that when filtering it wouldn't remove any of the results. It can be on a seperate worksheet, but would prefer it to be on the same sheet.

XL2007 is correct.

The data is in a table, so does that change anything on your first step you gave me?

**DonkeyOte** · 01-05-2011, 06:33 PM

Perhaps simplest to illustrate with a working example - see attached.

**cityofnr** · 01-06-2011, 10:54 AM

That did help. Thank you. I have it working except one issue. The first row of the data table where I put the formula and copy down isn't pulling anything into it. The next rows have the correct information, but that one is blank. When I go to the cell, the formula is there, but nothing appears. Any thoughts?

ETA: Nevermind, I think I got it. I had the wrong cell in for the date so it was pulling the one above it. Seems to be fine now. Thanks for your help!