| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function
I have the following formula -
=SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$4 000=DATEVALUE("4/25/2008"),1,0)))-SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$40 00DATEVALUE("5/1/2008"),1,0))) I want to include another datevalue range and I'm not sure how to incorporate it into this formula. Like if Cells O = Yes (E2) and Cells L = Certain Date Range and then I want to add another If Cells P = another certain date range. It will then count total number of accounts. Criterial in the end being 1. Has to equal Yes 2. Contract date needs to equal specific Date Range 3. Visited date needs to equal specific Date Range Then it counts total number of accounts with the above criteria. Is it possible to add to date ranges to the formula?? Thanks Brooke |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function
Try this:
=SUMPRODUCT(--(FAWAD!$O$2:$O$4000=$E$2),--(FAWAD!$L$2:$L$4000=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008"))) HTH, Paul -- "Brooke" wrote in message ... I have the following formula - =SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$4 000=DATEVALUE("4/25/2008"),1,0)))-SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$40 00DATEVALUE("5/1/2008"),1,0))) I want to include another datevalue range and I'm not sure how to incorporate it into this formula. Like if Cells O = Yes (E2) and Cells L = Certain Date Range and then I want to add another If Cells P = another certain date range. It will then count total number of accounts. Criterial in the end being 1. Has to equal Yes 2. Contract date needs to equal specific Date Range 3. Visited date needs to equal specific Date Range Then it counts total number of accounts with the above criteria. Is it possible to add to date ranges to the formula?? Thanks Brooke |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function
That gives me the first date range, but I also want to include another column
like K with the same datevalue range. So both columns have to have that date range before it will count. Can I just do another (--(FAWAD!$L$2:$L$4000=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008"))) ????? "PCLIVE" wrote: Try this: =SUMPRODUCT(--(FAWAD!$O$2:$O$4000=$E$2),--(FAWAD!$L$2:$L$4000=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008"))) HTH, Paul -- "Brooke" wrote in message ... I have the following formula - =SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$4 000=DATEVALUE("4/25/2008"),1,0)))-SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$40 00DATEVALUE("5/1/2008"),1,0))) I want to include another datevalue range and I'm not sure how to incorporate it into this formula. Like if Cells O = Yes (E2) and Cells L = Certain Date Range and then I want to add another If Cells P = another certain date range. It will then count total number of accounts. Criterial in the end being 1. Has to equal Yes 2. Contract date needs to equal specific Date Range 3. Visited date needs to equal specific Date Range Then it counts total number of accounts with the above criteria. Is it possible to add to date ranges to the formula?? Thanks Brooke |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function
If I understand you correctly then yes.
Heres the breakdown. The formula below will count rows that include all of the following criteria. -FAWAD!$O$2:$O$4000 These rows have to equal what is in $E$2 -FAWAD!$L$2:$L$4000 These rows have to be greater than or equal to 4/25/2008 AND... They have to be less than or equal to 5/1/2008 -FAWAD!$K$2:$K$4000 These rows have to be greater than or equal to 4/25/2008 AND... They have to be less than or equal to 5/1/2008 All of this criteria must be met in order to be counted. =SUMPRODUCT(--(FAWAD!$O$2:$O$4000=$E$2),--(FAWAD!$L$2:$L$4000=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008")),--(FAWAD!$K$2:$K$4000=DATEVALUE("4/25/2008")),--(FAWAD!$K$2:$K$4000<=DATEVALUE("5/1/2008"))) Regards, Paul -- "Brooke" wrote in message ... That gives me the first date range, but I also want to include another column like K with the same datevalue range. So both columns have to have that date range before it will count. Can I just do another (--(FAWAD!$L$2:$L$4000=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008"))) ????? "PCLIVE" wrote: Try this: =SUMPRODUCT(--(FAWAD!$O$2:$O$4000=$E$2),--(FAWAD!$L$2:$L$4000=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008"))) HTH, Paul -- "Brooke" wrote in message ... I have the following formula - =SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$4 000=DATEVALUE("4/25/2008"),1,0)))-SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$40 00DATEVALUE("5/1/2008"),1,0))) I want to include another datevalue range and I'm not sure how to incorporate it into this formula. Like if Cells O = Yes (E2) and Cells L = Certain Date Range and then I want to add another If Cells P = another certain date range. It will then count total number of accounts. Criterial in the end being 1. Has to equal Yes 2. Contract date needs to equal specific Date Range 3. Visited date needs to equal specific Date Range Then it counts total number of accounts with the above criteria. Is it possible to add to date ranges to the formula?? Thanks Brooke |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function
Perfect. Thank you so much.
"PCLIVE" wrote: If I understand you correctly then yes. Heres the breakdown. The formula below will count rows that include all of the following criteria. -FAWAD!$O$2:$O$4000 These rows have to equal what is in $E$2 -FAWAD!$L$2:$L$4000 These rows have to be greater than or equal to 4/25/2008 AND... They have to be less than or equal to 5/1/2008 -FAWAD!$K$2:$K$4000 These rows have to be greater than or equal to 4/25/2008 AND... They have to be less than or equal to 5/1/2008 All of this criteria must be met in order to be counted. =SUMPRODUCT(--(FAWAD!$O$2:$O$4000=$E$2),--(FAWAD!$L$2:$L$4000=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008")),--(FAWAD!$K$2:$K$4000=DATEVALUE("4/25/2008")),--(FAWAD!$K$2:$K$4000<=DATEVALUE("5/1/2008"))) Regards, Paul -- "Brooke" wrote in message ... That gives me the first date range, but I also want to include another column like K with the same datevalue range. So both columns have to have that date range before it will count. Can I just do another (--(FAWAD!$L$2:$L$4000=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008"))) ????? "PCLIVE" wrote: Try this: =SUMPRODUCT(--(FAWAD!$O$2:$O$4000=$E$2),--(FAWAD!$L$2:$L$4000=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008"))) HTH, Paul -- "Brooke" wrote in message ... I have the following formula - =SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$4 000=DATEVALUE("4/25/2008"),1,0)))-SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$40 00DATEVALUE("5/1/2008"),1,0))) I want to include another datevalue range and I'm not sure how to incorporate it into this formula. Like if Cells O = Yes (E2) and Cells L = Certain Date Range and then I want to add another If Cells P = another certain date range. It will then count total number of accounts. Criterial in the end being 1. Has to equal Yes 2. Contract date needs to equal specific Date Range 3. Visited date needs to equal specific Date Range Then it counts total number of accounts with the above criteria. Is it possible to add to date ranges to the formula?? Thanks Brooke |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
| Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions | |||
Linear Mode
