Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total using 2 columns for criteria
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total using 2 columns for criteria
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total using 2 columns for criteria
Could you expand on the pivot table, I have never gotten them to work.
Thanks. "Jan Karel Pieterse" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total using 2 columns for criteria
Hi Lost,
Could you expand on the pivot table, I have never gotten them to work. Su - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Total column changes colors when total equals sum of other columns | New Users to Excel | |||
Counting total for multiple criteria | Excel Worksheet Functions | |||
Sum Total # Of Cells That Meet Several Criteria | Excel Worksheet Functions | |||
get a total based on criteria in two columns | Excel Worksheet Functions | |||
Dividing a Total with criteria... | Excel Discussion (Misc queries) |