Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formula - sum "x" column L only when same row contains "y" columns
One column has unit locations at a facility and an array of three columns has
"qualifications" meeting other criteria of events at same facility. Each row represents an "unique identity". Need to create a formula that only counts a particular "unit location" if it shares the same row ("unique identity") that meets a particular "qualification" appearing among other "qualifications" within the three column array. Example: Count (get a sum of) only the "unique identities" that are in room "A" (unit location) and which wear the color black (qualifications). In other words, if "A" matches cells in same row that contain "black", what is the sum of "A's". |
#2
|
|||
|
|||
not too clear but
=sumproduct((a2:a22="a")*(b2:b22="black")) to count =sumproduct((a2:a22="a")*(b2:b22="black")*c2:c22)t o sum c based on criteria -- Don Guillett SalesAid Software "Psalm91jim" wrote in message ... One column has unit locations at a facility and an array of three columns has "qualifications" meeting other criteria of events at same facility. Each row represents an "unique identity". Need to create a formula that only counts a particular "unit location" if it shares the same row ("unique identity") that meets a particular "qualification" appearing among other "qualifications" within the three column array. Example: Count (get a sum of) only the "unique identities" that are in room "A" (unit location) and which wear the color black (qualifications). In other words, if "A" matches cells in same row that contain "black", what is the sum of "A's". |
#3
|
|||
|
|||
Thanks Don for trying to help but that formula only generated #N/A.
If I may try to explain the situation better. Picture (5000 +/-) rows of patients and (100 +/-) columns with many specifics about each patient€”personal to institutional data. Some data requires using three columns due to the nature of the form from which the data is manually retrieved (i.e., person filling out form can put down multiple entries in one category€”best three choices, for instance), hence requiring three columns for that one category. Not every cell in the three columns has data in it. I need a formula that will only add up the patients who share common attributes from different categories, ignoring all other entries. Attribute One: Patient stayed in unit Surgery (that info appears in one column on my worksheet€”column AZ row 109). Attribute Two: Patient required procedure CPR (that info appears within three columns under one category, though for each patient it appears only once in one of the columns intersecting the row the patients information is on). For instance: Under the category requiring three columns called Procedures: CPR - column AT row 109, Ventilation - Column AU row 109, blank cell - column AV row 109. I just need to count only the patients that stayed in unit Surgery that required CPR over the course of a year. A AT AU AV AZ 1 Patients Proc Proc Proc Nursing Unit 107 Patient A CPR Tube Feeding Ventilation Surgery 108 Patient B Antibiotics Emergency Room 109 Patient C CPR Ventilation Surgery For the above model, the obvious answer to how many patients received CPR in the Surgery Unit would be two (2). But when I am dealing with thousands of patients and I am using an Excel program, what is the best formula for gleaning that information from the data as presented above. If you or anyone else can help, I sure would appreciate this. Thank you for what you have done already. Only By His Grace Jim "Don Guillett" wrote: not too clear but =sumproduct((a2:a22="a")*(b2:b22="black")) to count =sumproduct((a2:a22="a")*(b2:b22="black")*c2:c22)t o sum c based on criteria -- Don Guillett SalesAid Software "Psalm91jim" wrote in message ... One column has unit locations at a facility and an array of three columns has "qualifications" meeting other criteria of events at same facility. Each row represents an "unique identity". Need to create a formula that only counts a particular "unit location" if it shares the same row ("unique identity") that meets a particular "qualification" appearing among other "qualifications" within the three column array. Example: Count (get a sum of) only the "unique identities" that are in room "A" (unit location) and which wear the color black (qualifications). In other words, if "A" matches cells in same row that contain "black", what is the sum of "A's". |
#4
|
|||
|
|||
With a lot of work you can maintain this in Excel and fight through the logic
of different types of data requests like this. For the same amount of effort, and maybe even less, you can put your data into a database like MS Access or MSDE and use a powerful query feature to summarize, analyze, and/or extract data. Excel even has a query tool that allows you to pull subsets of the data from the database into a spreadsheet. "psalm91jim" wrote: Thanks Don for trying to help but that formula only generated #N/A. If I may try to explain the situation better. Picture (5000 +/-) rows of patients and (100 +/-) columns with many specifics about each patient€”personal to institutional data. Some data requires using three columns due to the nature of the form from which the data is manually retrieved (i.e., person filling out form can put down multiple entries in one category€”best three choices, for instance), hence requiring three columns for that one category. Not every cell in the three columns has data in it. I need a formula that will only add up the patients who share common attributes from different categories, ignoring all other entries. Attribute One: Patient stayed in unit Surgery (that info appears in one column on my worksheet€”column AZ row 109). Attribute Two: Patient required procedure CPR (that info appears within three columns under one category, though for each patient it appears only once in one of the columns intersecting the row the patients information is on). For instance: Under the category requiring three columns called Procedures: CPR - column AT row 109, Ventilation - Column AU row 109, blank cell - column AV row 109. I just need to count only the patients that stayed in unit Surgery that required CPR over the course of a year. A AT AU AV AZ 1 Patients Proc Proc Proc Nursing Unit 107 Patient A CPR Tube Feeding Ventilation Surgery 108 Patient B Antibiotics Emergency Room 109 Patient C CPR Ventilation Surgery For the above model, the obvious answer to how many patients received CPR in the Surgery Unit would be two (2). But when I am dealing with thousands of patients and I am using an Excel program, what is the best formula for gleaning that information from the data as presented above. If you or anyone else can help, I sure would appreciate this. Thank you for what you have done already. Only By His Grace Jim "Don Guillett" wrote: not too clear but =sumproduct((a2:a22="a")*(b2:b22="black")) to count =sumproduct((a2:a22="a")*(b2:b22="black")*c2:c22)t o sum c based on criteria -- Don Guillett SalesAid Software "Psalm91jim" wrote in message ... One column has unit locations at a facility and an array of three columns has "qualifications" meeting other criteria of events at same facility. Each row represents an "unique identity". Need to create a formula that only counts a particular "unit location" if it shares the same row ("unique identity") that meets a particular "qualification" appearing among other "qualifications" within the three column array. Example: Count (get a sum of) only the "unique identities" that are in room "A" (unit location) and which wear the color black (qualifications). In other words, if "A" matches cells in same row that contain "black", what is the sum of "A's". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reference every "n" cell in a column in Excel? | Excel Worksheet Functions | |||
XL formula - total of row = total of column | Excel Worksheet Functions | |||
Move column values w/o formula | Excel Worksheet Functions | |||
how to enter a formula using column() function for a range | Excel Worksheet Functions | |||
Adding TR to the end of each value in a column..formula | Excel Worksheet Functions |