| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
How do I get an IF statement to pull a date range??
I've created an IF statement with multiple formulas, but I can't seem to
figure out if it is possible to pull the information by a specific date range. I have sales reps that visit customers on specific dates and I need my tally sheet to only pull dates within that current week. Does anyone know if this is possible?? This is what I have so far: =SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales rep!P2:P216=I7,IF(sales rep!$N$2:$N$216=DATEVALUE("3/18/2005"),1,0))))) If you look above I have it to calculate the date value starting at 03-18-05, but I would like it to only pull that date through the next week. Help??? :) |
|
#2
|
|||
|
|||
|
Take a look at Data Filter AutoFilter Custom, and select GreaterThan
and LesserThan values..............this will filter out everything except those within your desired date range. Vaya con Dios, Chuck, CABGx3 "Brooke Medvecky" <Brooke wrote in message ... I've created an IF statement with multiple formulas, but I can't seem to figure out if it is possible to pull the information by a specific date range. I have sales reps that visit customers on specific dates and I need my tally sheet to only pull dates within that current week. Does anyone know if this is possible?? This is what I have so far: =SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales rep!P2:P216=I7,IF(sales rep!$N$2:$N$216=DATEVALUE("3/18/2005"),1,0))))) If you look above I have it to calculate the date value starting at 03-18-05, but I would like it to only pull that date through the next week. Help??? :) |
|
#3
|
|||
|
|||
|
I realize that I can Autofilter my whole workbook, but I want to eliminte
doing that hand filter stuff. I have a different worksheet that has a tally sheet that I would like to reference instead of going into 20 sales reps worksheets to figure out who they've seen each week. Is it possible to write an IF statement to calculate only date ranges??? DATEVALUE("3/18/05-"3/24/05") I've tried this and it doesn't work. There has to be a way to pull a range. Help??? "CLR" wrote: Take a look at Data Filter AutoFilter Custom, and select GreaterThan and LesserThan values..............this will filter out everything except those within your desired date range. Vaya con Dios, Chuck, CABGx3 "Brooke Medvecky" <Brooke wrote in message ... I've created an IF statement with multiple formulas, but I can't seem to figure out if it is possible to pull the information by a specific date range. I have sales reps that visit customers on specific dates and I need my tally sheet to only pull dates within that current week. Does anyone know if this is possible?? This is what I have so far: =SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales rep!P2:P216=I7,IF(sales rep!$N$2:$N$216=DATEVALUE("3/18/2005"),1,0))))) If you look above I have it to calculate the date value starting at 03-18-05, but I would like it to only pull that date through the next week. Help??? :) |
|
#4
|
|||
|
|||
|
Assuming your dates are in column A, and your values in column B, then enter
a StartDate in E1 and put this formula in C1 and copy down......only those rows within the date range of E1+6 will show up.........then sum them, average them or whatever........ =IF(AND(A1=$E$1,A1<=$E$1+6),B1,"") BTW, Autofilter will run under macro control....... hth Vaya con Dios, Chuck, CABGx3 "Brooke Medvecky" wrote in message ... I realize that I can Autofilter my whole workbook, but I want to eliminte doing that hand filter stuff. I have a different worksheet that has a tally sheet that I would like to reference instead of going into 20 sales reps worksheets to figure out who they've seen each week. Is it possible to write an IF statement to calculate only date ranges??? DATEVALUE("3/18/05-"3/24/05") I've tried this and it doesn't work. There has to be a way to pull a range. Help??? "CLR" wrote: Take a look at Data Filter AutoFilter Custom, and select GreaterThan and LesserThan values..............this will filter out everything except those within your desired date range. Vaya con Dios, Chuck, CABGx3 "Brooke Medvecky" <Brooke wrote in message ... I've created an IF statement with multiple formulas, but I can't seem to figure out if it is possible to pull the information by a specific date range. I have sales reps that visit customers on specific dates and I need my tally sheet to only pull dates within that current week. Does anyone know if this is possible?? This is what I have so far: =SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales rep!P2:P216=I7,IF(sales rep!$N$2:$N$216=DATEVALUE("3/18/2005"),1,0))))) If you look above I have it to calculate the date value starting at 03-18-05, but I would like it to only pull that date through the next week. Help??? :) |
|
#5
|
|||
|
|||
|
or maybe...........just this one formula to give you the sum of the criteria
values...... =SUMIF(A1:A100,"<="&E1+6,B1:B100)-SUMIF(A1:A100,"<"&E1,B1:B100) Again assuming Dates in column A, values in column B, and StartDate in E1. Vaya con Dios, Chuck, CABGx3 "CLR" wrote in message ... Assuming your dates are in column A, and your values in column B, then enter a StartDate in E1 and put this formula in C1 and copy down......only those rows within the date range of E1+6 will show up.........then sum them, average them or whatever........ =IF(AND(A1=$E$1,A1<=$E$1+6),B1,"") BTW, Autofilter will run under macro control....... hth Vaya con Dios, Chuck, CABGx3 "Brooke Medvecky" wrote in message ... I realize that I can Autofilter my whole workbook, but I want to eliminte doing that hand filter stuff. I have a different worksheet that has a tally sheet that I would like to reference instead of going into 20 sales reps worksheets to figure out who they've seen each week. Is it possible to write an IF statement to calculate only date ranges??? DATEVALUE("3/18/05-"3/24/05") I've tried this and it doesn't work. There has to be a way to pull a range. Help??? "CLR" wrote: Take a look at Data Filter AutoFilter Custom, and select GreaterThan and LesserThan values..............this will filter out everything except those within your desired date range. Vaya con Dios, Chuck, CABGx3 "Brooke Medvecky" <Brooke wrote in message ... I've created an IF statement with multiple formulas, but I can't seem to figure out if it is possible to pull the information by a specific date range. I have sales reps that visit customers on specific dates and I need my tally sheet to only pull dates within that current week. Does anyone know if this is possible?? This is what I have so far: =SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales rep!P2:P216=I7,IF(sales rep!$N$2:$N$216=DATEVALUE("3/18/2005"),1,0))))) If you look above I have it to calculate the date value starting at 03-18-05, but I would like it to only pull that date through the next week. Help??? :) |
|
#6
|
|||
|
|||
|
I'm sorry that I'm not quite getting it, but I'm kinda new to these If
statements. I understand the formula, but how would I get it to fit into my whole if statement of =SUM(IF(salesrep!AB2:AB216=I3,IF(salesrep!AA2:AA21 6=I4,IF(salesrep!P2:p216=I7,IF(salesrep!N2:N216 ????? Date Range issue. Assuming that AB = One criteria I'm pulling Assuming that AA = Second criteria Assuming that P = Third Criteria Assuming that N= all the visited dates If I put a start date in E1 then how would the formula look inside the above instead of a single IF statement for the date ranges. I need the formula to recognize the above criterias also. I hope this makes sense. Thanks for your time. "CLR" wrote: or maybe...........just this one formula to give you the sum of the criteria values...... =SUMIF(A1:A100,"<="&E1+6,B1:B100)-SUMIF(A1:A100,"<"&E1,B1:B100) Again assuming Dates in column A, values in column B, and StartDate in E1. Vaya con Dios, Chuck, CABGx3 "CLR" wrote in message ... Assuming your dates are in column A, and your values in column B, then enter a StartDate in E1 and put this formula in C1 and copy down......only those rows within the date range of E1+6 will show up.........then sum them, average them or whatever........ =IF(AND(A1=$E$1,A1<=$E$1+6),B1,"") BTW, Autofilter will run under macro control....... hth Vaya con Dios, Chuck, CABGx3 "Brooke Medvecky" wrote in message ... I realize that I can Autofilter my whole workbook, but I want to eliminte doing that hand filter stuff. I have a different worksheet that has a tally sheet that I would like to reference instead of going into 20 sales reps worksheets to figure out who they've seen each week. Is it possible to write an IF statement to calculate only date ranges??? DATEVALUE("3/18/05-"3/24/05") I've tried this and it doesn't work. There has to be a way to pull a range. Help??? "CLR" wrote: Take a look at Data Filter AutoFilter Custom, and select GreaterThan and LesserThan values..............this will filter out everything except those within your desired date range. Vaya con Dios, Chuck, CABGx3 "Brooke Medvecky" <Brooke wrote in message ... I've created an IF statement with multiple formulas, but I can't seem to figure out if it is possible to pull the information by a specific date range. I have sales reps that visit customers on specific dates and I need my tally sheet to only pull dates within that current week. Does anyone know if this is possible?? This is what I have so far: =SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales rep!P2:P216=I7,IF(sales rep!$N$2:$N$216=DATEVALUE("3/18/2005"),1,0))))) If you look above I have it to calculate the date value starting at 03-18-05, but I would like it to only pull that date through the next week. Help??? :) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get an IF statement to pull a date range??
If i wanted a cell to equal a value if a particular cell was within a certain range, how would i accomplish that? For example: Cell A1=51 and cell B2 to equal 10 if cell A1 is between 50 and 60 I can do this easily for greater then or equal to but not when combining the 50 and <60 particulars. Please help! -- Browner ------------------------------------------------------------------------ Browner's Profile: http://www.excelforum.com/member.php...o&userid=33657 View this thread: http://www.excelforum.com/showthread...hreadid=357335 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get an IF statement to pull a date range??
in B2
=if(and(A150,A1<60),10,?) ? what if NOT true ... what is B2? "Browner" wrote: If i wanted a cell to equal a value if a particular cell was within a certain range, how would i accomplish that? For example: Cell A1=51 and cell B2 to equal 10 if cell A1 is between 50 and 60 I can do this easily for greater then or equal to but not when combining the 50 and <60 particulars. Please help! -- Browner ------------------------------------------------------------------------ Browner's Profile: http://www.excelforum.com/member.php...o&userid=33657 View this thread: http://www.excelforum.com/showthread...hreadid=357335 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date in an IF statement | Excel Worksheet Functions | |||
| Completion Percentage of a date range | Excel Discussion (Misc queries) | |||
| How do I set a date range for conditional formatting in a macro? | Excel Worksheet Functions | |||
| SUMPRODUCT with date range question | Excel Discussion (Misc queries) | |||
| How can I sum information in a list with a date range? | Excel Worksheet Functions | |||
Hybrid Mode
