Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sum(if) data matching two (or more) separated columns
How do I collect a sum with the following conditions(?):
If A:A = "AAA" If D:D = "XXX" then sum of all "XXX" that also are "AAA" (i.e., appear in the same row as "AAA") Along the same lines consider also: If A:A = "AAA" and, If C:C = "BBB" and, If D:D= "XXX" then, sum of all "XXX" that are also "AAA" and "BBB" when "AAA" and "BBB" appear in the same row with "XXX" Thanks Jim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sum(if) data matching two (or more) separated columns
Try,
=SUMPRODUCT((A1:A20000="XXX")*(D1:D20000="XXX")) and =SUMPRODUCT((A1:A20000="XXX")*(C1:C20000="BBB")*(D 1:D20000="XXX")) You can't use full columns Mike "psalm91jim" wrote: How do I collect a sum with the following conditions(?): If A:A = "AAA" If D:D = "XXX" then sum of all "XXX" that also are "AAA" (i.e., appear in the same row as "AAA") Along the same lines consider also: If A:A = "AAA" and, If C:C = "BBB" and, If D:D= "XXX" then, sum of all "XXX" that are also "AAA" and "BBB" when "AAA" and "BBB" appear in the same row with "XXX" Thanks Jim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sum(if) data matching two (or more) separated columns
Do you want a COUNT or a SUM?
Your description sounds like a COUNT. =SUMPRODUCT(--(A1:A10="AAA"),--(D1:D10="XXX")) =SUMPRODUCT(--(A1:A10="AAA"),--(C1:C10="BBB"),--(D1:D10="XXX")) Better to use cells to hold the criteria: X1 = AAA X2 = BBB X3 = XXX =SUMPRODUCT(--(A1:A10=X1),--(D1:D10=X3)) =SUMPRODUCT(--(A1:A10=X1),--(C1:C10=X2),--(D1:D10=X3)) -- Biff Microsoft Excel MVP "psalm91jim" wrote in message ... How do I collect a sum with the following conditions(?): If A:A = "AAA" If D:D = "XXX" then sum of all "XXX" that also are "AAA" (i.e., appear in the same row as "AAA") Along the same lines consider also: If A:A = "AAA" and, If C:C = "BBB" and, If D:D= "XXX" then, sum of all "XXX" that are also "AAA" and "BBB" when "AAA" and "BBB" appear in the same row with "XXX" Thanks Jim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sum(if) data matching two (or more) separated colu
"Roger Govier" wrote: Hi Jim =SUMPRODUCT(--(A1:A1000)="AAA"),--(C1:C1000="BBB"),--(D1:D1000="XXX")) -- Regards Roger Govier "psalm91jim" wrote in message ... How do I collect a sum with the following conditions(?): If A:A = "AAA" If D:D = "XXX" then sum of all "XXX" that also are "AAA" (i.e., appear in the same row as "AAA") Along the same lines consider also: If A:A = "AAA" and, If C:C = "BBB" and, If D:D= "XXX" then, sum of all "XXX" that are also "AAA" and "BBB" when "AAA" and "BBB" appear in the same row with "XXX" Thanks Jim Thanks for your quick response Roger but that formula generated for me #NUM. Perhaps I was not clear in describing the scenario. Let me try to show it a different way: Out of a possible 200+ columns of data on patients, I want to find how many patients meet the following criteria: How many patients that are Pulmonary consults were treated in a timely manner while in ICU. One of the columns contains which service referred the consult. For instance, column AI lists by patient who referred the consult: Cardiology, Oncology, Pulmonary, etc., as text data. Another column (BB), contains text data which describes how timely medical attention was received by patient: Some/Timely, Full/Timely, Minimal, etc. And finally another column (GH) contains text data which describes in what department the patients first were seen: ICU, PCU, Nursing Floor, etc. Each of these columns may have the possibility of being answered by one of 4-20 responses (depending on what the column is requiring within the parameters set for the appropriated--and only--answer). From the three columns I want to find the number of patients that meet the following condition: AI="Pulmonary"; BB="Full/Timely"; and GH="ICU". The answer should reveal a sum of patient meeting those conditions from a pool of all patients seen in a fiscal year. For instance, though I may have seen 1500 patients in Fiscal Year 2008 (each row is a different patient), only 45 met the conditions stated above: They were referred to my department by "pulmonary" MD, their symptoms were addressed in a "full/timely" manner while they were in the care of the "ICU" department. I have created a number of charts that track compilation of data from the large fiscal year spreed sheet and this formula is for one item in a particular chart. I appreciate your help. For the past few years I have attempted to find an answer to this query but have been unable to. Looked in various help books but nothing seems to address this particular conditional need (or at least I have been unable to find it). Thanks for your kind help Jim Bowers (psalm91jim) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sum(if) data matching two (or more) separated colu
Hi Jim
That should have produce a correct result provided your data is OK. You would have needed to make the ranges fit your requirement, (you cannot use whole columns) and each range would have to have the same dimensions. Post back with the actual formula you used. if you are still having difficulties, mail me a copy of the workbook direct and I will take a look. To send direct roger at technology4u dot co dot uk Change the at and dots to make a valid email address, -- Regards Roger Govier "psalm91jim" wrote in message ... "Roger Govier" wrote: Hi Jim =SUMPRODUCT(--(A1:A1000)="AAA"),--(C1:C1000="BBB"),--(D1:D1000="XXX")) -- Regards Roger Govier "psalm91jim" wrote in message ... How do I collect a sum with the following conditions(?): If A:A = "AAA" If D:D = "XXX" then sum of all "XXX" that also are "AAA" (i.e., appear in the same row as "AAA") Along the same lines consider also: If A:A = "AAA" and, If C:C = "BBB" and, If D:D= "XXX" then, sum of all "XXX" that are also "AAA" and "BBB" when "AAA" and "BBB" appear in the same row with "XXX" Thanks Jim Thanks for your quick response Roger but that formula generated for me #NUM. Perhaps I was not clear in describing the scenario. Let me try to show it a different way: Out of a possible 200+ columns of data on patients, I want to find how many patients meet the following criteria: How many patients that are Pulmonary consults were treated in a timely manner while in ICU. One of the columns contains which service referred the consult. For instance, column AI lists by patient who referred the consult: Cardiology, Oncology, Pulmonary, etc., as text data. Another column (BB), contains text data which describes how timely medical attention was received by patient: Some/Timely, Full/Timely, Minimal, etc. And finally another column (GH) contains text data which describes in what department the patients first were seen: ICU, PCU, Nursing Floor, etc. Each of these columns may have the possibility of being answered by one of 4-20 responses (depending on what the column is requiring within the parameters set for the appropriated--and only--answer). From the three columns I want to find the number of patients that meet the following condition: AI="Pulmonary"; BB="Full/Timely"; and GH="ICU". The answer should reveal a sum of patient meeting those conditions from a pool of all patients seen in a fiscal year. For instance, though I may have seen 1500 patients in Fiscal Year 2008 (each row is a different patient), only 45 met the conditions stated above: They were referred to my department by "pulmonary" MD, their symptoms were addressed in a "full/timely" manner while they were in the care of the "ICU" department. I have created a number of charts that track compilation of data from the large fiscal year spreed sheet and this formula is for one item in a particular chart. I appreciate your help. For the past few years I have attempted to find an answer to this query but have been unable to. Looked in various help books but nothing seems to address this particular conditional need (or at least I have been unable to find it). Thanks for your kind help Jim Bowers (psalm91jim) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
matching 2 columns of data | Excel Discussion (Misc queries) | |||
Conditional sum matching two columns and a row | Excel Worksheet Functions | |||
Matching two columns and their data | Excel Worksheet Functions | |||
Matching Data in Columns | Excel Worksheet Functions | |||
Matching data from 2 columns | Excel Worksheet Functions |