Total using 2 columns for criteria

[Guest]

I have a list of blood cultures that I need to total how many are done on a
given day and how many per patient.

The list looks like this:
Patient Name MRN Accession REC DT
Cole Gate 12345 12-022-0254 22JAN07
Cole Gate 12345 12-030-2549 30JAN07
Aurthur Itis 67890 12-006-1976 06JAN07
Aurthur Itis 67890 12-008-3334 08JAN07
Aurthur Itis 67890 12-009-0250 09JAN07
Aurthur Itis 67890 12-010-3908 10JAN07
Aurthur Itis 67890 12-010-3916 10JAN07
Aurthur Itis 67890 12-012-1330 12JAN07
Aurthur Itis 67890 12-012-1786 12JAN07

What I would like is to have a running total of blood cultures per patient
and per rec date. If I use subtotal, I can only get "at every change in MRN"
a total, what I would like is IF (MRN from Row A = MRN from ROW B) AND (REC
date from row A =REC date from Row B) then count them and put the total in
another column, if they are not the same count and put the total in the same
column.

So the output for the above would be something like:
Patient Name MRN Accession REC DT Total
Cole Gate 12345 12-022-0254 22JAN07 1
Cole Gate 12345 12-030-2549 30JAN07 1
Aurthur Itis 67890 12-006-1976 06JAN07 1
Aurthur Itis 67890 12-008-3334 08JAN07 1
Aurthur Itis 67890 12-009-0250 09JAN07 1
Aurthur Itis 67890 12-010-3908 10JAN07 2
Aurthur Itis 67890 12-010-3916 10JAN07
Aurthur Itis 67890 12-012-1330 12JAN07 2
Aurthur Itis 67890 12-012-1786 12JAN07

Sorry for the lengthy question, but I thought this was the clearest
explanation. I have had trouble coming up with the exact right function, and
am not very good at writing macros.

Thanks for any help,
Lost in Microbiology lab

 

[Jan Karel Pieterse]

Hi Lost,

What I would like is to have a running total of blood cultures per patient
and per rec date.

This calls for a pivot table.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Guest]

Could you expand on the pivot table, I have never gotten them to work.

Thanks.

 

[Jan Karel Pieterse]

Hi Lost,

Could you expand on the pivot table, I have never gotten them to work.

Sure:

- select table (if this one expands over time, select entire columns)
- Data, pivot table, finish
- drag the first three columns to the row area
- rightclick each column in that row area, select "Field Settings..."
- Select "None" for subtotals
- drag the REC DT to the data area and you're done.

Now after entering new data, just refresh your pivot table and you're
done.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com