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".

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".

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".

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".