Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I can't get my formula to do what I want it to do..........HELP!
Column E Column H Column Y
Program # of Repeat Svs Completion Status IOP 2 Completed OP 1 ASA CIC 0 Admin Disc TP 4 Completed IOP 3 Completed IOP 2 ASA In the above example, I want to look at home many times an individual in a particular program (Column E), has returned for services (Column H). I am also looking at the individuals completion status (Column Y). Using the example above, I should be obtaining the following outcomes: I am also looking at particular dates of service (which the formula below includes). Unforuntaely, the formula I have written doesn't accurately reflect the column H information I desire. The current formula only tells me how many occurances greater than 0, and indivdual has had repeat services. In order words, it would tell me that In our IOP Program, (3) people had repeat services (only counting the "occurance" of repeat services, and not the actual "summing" of the number of times an individual has returned for services. I had also not included the Column Y parameter which looks at the individuals "completion" status, and was having difficulty encorporating that informtion into my formaula............Any help would be appreciated. The formula below is what I currently have, but obviously I need to modify and expand this formula in order to get what I want.....................MUCH THANKS in Advance, Dan =SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,4,1)),--('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)),--('Raw Data'!$H$4:$H$50000),--('Raw Data'!$E$4:$E$5000="IOP")) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I can't get my formula to do what I want it to do..........HELP!
In this formula:
=SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,4,1)), --('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)), --('Raw Data'!$H$4:$H$50000), --('Raw Data'!$E$4:$E$5000="IOP")) You're counting the number of rows where all these things are true at the same time. If you just want to sum column H, you could use: =SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,4,1)), --('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)), ('Raw Data'!$H$4:$H$5000), --('Raw Data'!$E$4:$E$5000="IOP")) The -- converts true/false's to 1/0's. I don't need to do that conversion for column H. They're already numbers. But this will sum everything in column H. Both 0's (which wouldn't matter) and negatives (which couldn't happen in your data). But if you're doing this where those values could be negative, you could use: =SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,4,1)), --('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)), --('Raw Data'!$H$4:$H$50000), --('Raw Data'!$E$4:$E$5000="IOP"), ('Raw Data'!$H$4:$H$5000)) This still does the check for positive, but adds the values when all those checks are true. Dan the Man wrote: Column E Column H Column Y Program # of Repeat Svs Completion Status IOP 2 Completed OP 1 ASA CIC 0 Admin Disc TP 4 Completed IOP 3 Completed IOP 2 ASA In the above example, I want to look at home many times an individual in a particular program (Column E), has returned for services (Column H). I am also looking at the individuals completion status (Column Y). Using the example above, I should be obtaining the following outcomes: I am also looking at particular dates of service (which the formula below includes). Unforuntaely, the formula I have written doesn't accurately reflect the column H information I desire. The current formula only tells me how many occurances greater than 0, and indivdual has had repeat services. In order words, it would tell me that In our IOP Program, (3) people had repeat services (only counting the "occurance" of repeat services, and not the actual "summing" of the number of times an individual has returned for services. I had also not included the Column Y parameter which looks at the individuals "completion" status, and was having difficulty encorporating that informtion into my formaula............Any help would be appreciated. The formula below is what I currently have, but obviously I need to modify and expand this formula in order to get what I want.....................MUCH THANKS in Advance, Dan =SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,4,1)),--('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)),--('Raw Data'!$H$4:$H$50000),--('Raw Data'!$E$4:$E$5000="IOP")) -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I can't get my formula to do what I want it to do..........HELP!
You just need to add to the formula to get the total no. of service calls
('Raw Data'!$H$4:$H$5000) You can add other components on similar lines. You need to remember that if you have a condition in any component it will return 1 and 0 which gives you the count. If you want the sum of acutal values use them without any condition... There are innumerable posts on sumproduct... if this does not answer your question then search through them or add to this post again. "Dan the Man" wrote: Column E Column H Column Y Program # of Repeat Svs Completion Status IOP 2 Completed OP 1 ASA CIC 0 Admin Disc TP 4 Completed IOP 3 Completed IOP 2 ASA In the above example, I want to look at home many times an individual in a particular program (Column E), has returned for services (Column H). I am also looking at the individuals completion status (Column Y). Using the example above, I should be obtaining the following outcomes: I am also looking at particular dates of service (which the formula below includes). Unforuntaely, the formula I have written doesn't accurately reflect the column H information I desire. The current formula only tells me how many occurances greater than 0, and indivdual has had repeat services. In order words, it would tell me that In our IOP Program, (3) people had repeat services (only counting the "occurance" of repeat services, and not the actual "summing" of the number of times an individual has returned for services. I had also not included the Column Y parameter which looks at the individuals "completion" status, and was having difficulty encorporating that informtion into my formaula............Any help would be appreciated. The formula below is what I currently have, but obviously I need to modify and expand this formula in order to get what I want.....................MUCH THANKS in Advance, Dan =SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,4,1)),--('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)),--('Raw Data'!$H$4:$H$50000),--('Raw Data'!$E$4:$E$5000="IOP")) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I can't get my formula to do what I want it to do..........HEL
Thanks Dave. I learn something every day. And you are correct, that my data
for column h would never produce negatives, so the first formula alteration you made was the answer. Have a great weekend! Dan "Dave Peterson" wrote: In this formula: =SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,4,1)), --('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)), --('Raw Data'!$H$4:$H$50000), --('Raw Data'!$E$4:$E$5000="IOP")) You're counting the number of rows where all these things are true at the same time. If you just want to sum column H, you could use: =SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,4,1)), --('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)), ('Raw Data'!$H$4:$H$5000), --('Raw Data'!$E$4:$E$5000="IOP")) The -- converts true/false's to 1/0's. I don't need to do that conversion for column H. They're already numbers. But this will sum everything in column H. Both 0's (which wouldn't matter) and negatives (which couldn't happen in your data). But if you're doing this where those values could be negative, you could use: =SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,4,1)), --('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)), --('Raw Data'!$H$4:$H$50000), --('Raw Data'!$E$4:$E$5000="IOP"), ('Raw Data'!$H$4:$H$5000)) This still does the check for positive, but adds the values when all those checks are true. Dan the Man wrote: Column E Column H Column Y Program # of Repeat Svs Completion Status IOP 2 Completed OP 1 ASA CIC 0 Admin Disc TP 4 Completed IOP 3 Completed IOP 2 ASA In the above example, I want to look at home many times an individual in a particular program (Column E), has returned for services (Column H). I am also looking at the individuals completion status (Column Y). Using the example above, I should be obtaining the following outcomes: I am also looking at particular dates of service (which the formula below includes). Unforuntaely, the formula I have written doesn't accurately reflect the column H information I desire. The current formula only tells me how many occurances greater than 0, and indivdual has had repeat services. In order words, it would tell me that In our IOP Program, (3) people had repeat services (only counting the "occurance" of repeat services, and not the actual "summing" of the number of times an individual has returned for services. I had also not included the Column Y parameter which looks at the individuals "completion" status, and was having difficulty encorporating that informtion into my formaula............Any help would be appreciated. The formula below is what I currently have, but obviously I need to modify and expand this formula in order to get what I want.....................MUCH THANKS in Advance, Dan =SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,4,1)),--('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)),--('Raw Data'!$H$4:$H$50000),--('Raw Data'!$E$4:$E$5000="IOP")) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|