Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumifs, sumif with dates
Workbook to report all OT (overtime) performed during FY08 within a range of
date for comparison to current FY usage. Two worksheets, 'Report' and 'FY08'. 'FY08'!D:D contains the dates the OT was performed and 'FY08'F:F contains the number of hours performed for that date. Each row is another occurrence of OT performed. Worksheet 'Report' each row holds the two dates for the criteria, in column C (start date) and column E (end date), ie, 10/8/2007 and 10/12/2007. Equation for each row for sum of hours performed within date range. Problem: At home I have Excel 2007 and Excel 2003 at work. Solved the equation in Excel 2007 as follows: =sumifs('FY08'!F:F,'FY08'!D:D,"="&text(C2,"mm/dd/yyyy"),'FY08'!D:D,"<="&text(E2,""mm/dd/yyyy"). If there is a simpler way I'd love to see it. Back to the problem- At work, Excel 2003 there is no SUMIFS function. Can not get it to work. Tried SUMIF as follows: =sumif('FY08'!D:D,(and(('FY08'!DD,"="&text(C2,"mm/dd/yyyy")),('FY08'!D:D."<="&text(E2,"mm/dd/yyyy")))),'FY08'!F:F) Tried CTRL+SHIFT+ENTER, still no joy. Your suggestions please. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumifs, sumif with dates
Hi,
For the first formula you can use =SUMIFS('FY08'!F:F,'FY08'!D:D,"="&C1,'FY08'!D:D," <="&D1) I've changed to C1 and D1 and the dates are all entered as dates. If this helps, please click the Yes button, Cheers, Shane Devenshire "Richard Manor" wrote: Workbook to report all OT (overtime) performed during FY08 within a range of date for comparison to current FY usage. Two worksheets, 'Report' and 'FY08'. 'FY08'!D:D contains the dates the OT was performed and 'FY08'F:F contains the number of hours performed for that date. Each row is another occurrence of OT performed. Worksheet 'Report' each row holds the two dates for the criteria, in column C (start date) and column E (end date), ie, 10/8/2007 and 10/12/2007. Equation for each row for sum of hours performed within date range. Problem: At home I have Excel 2007 and Excel 2003 at work. Solved the equation in Excel 2007 as follows: =sumifs('FY08'!F:F,'FY08'!D:D,"="&text(C2,"mm/dd/yyyy"),'FY08'!D:D,"<="&text(E2,""mm/dd/yyyy"). If there is a simpler way I'd love to see it. Back to the problem- At work, Excel 2003 there is no SUMIFS function. Can not get it to work. Tried SUMIF as follows: =sumif('FY08'!D:D,(and(('FY08'!DD,"="&text(C2,"mm/dd/yyyy")),('FY08'!D:D."<="&text(E2,"mm/dd/yyyy")))),'FY08'!F:F) Tried CTRL+SHIFT+ENTER, still no joy. Your suggestions please. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumifs, sumif with dates
Hi,
In 2003 use =SUMPRODUCT(--(D:D=C1),--(D:D<=D1),F:F) Change the references as needed Cheers, Shane Devenshire "Richard Manor" wrote: Workbook to report all OT (overtime) performed during FY08 within a range of date for comparison to current FY usage. Two worksheets, 'Report' and 'FY08'. 'FY08'!D:D contains the dates the OT was performed and 'FY08'F:F contains the number of hours performed for that date. Each row is another occurrence of OT performed. Worksheet 'Report' each row holds the two dates for the criteria, in column C (start date) and column E (end date), ie, 10/8/2007 and 10/12/2007. Equation for each row for sum of hours performed within date range. Problem: At home I have Excel 2007 and Excel 2003 at work. Solved the equation in Excel 2007 as follows: =sumifs('FY08'!F:F,'FY08'!D:D,"="&text(C2,"mm/dd/yyyy"),'FY08'!D:D,"<="&text(E2,""mm/dd/yyyy"). If there is a simpler way I'd love to see it. Back to the problem- At work, Excel 2003 there is no SUMIFS function. Can not get it to work. Tried SUMIF as follows: =sumif('FY08'!D:D,(and(('FY08'!DD,"="&text(C2,"mm/dd/yyyy")),('FY08'!D:D."<="&text(E2,"mm/dd/yyyy")))),'FY08'!F:F) Tried CTRL+SHIFT+ENTER, still no joy. Your suggestions please. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumifs, sumif with dates
That helped, using the expression "<="&C1 work just as
"<="&text(C1,"mm/dd/yyyy"), the same value is returned. Using Evaluate Formula Tried it in =sumif('sheet'!D:D,"<="&E4,'sheet'!F:F) E4 being 10/12/2007, the criteria came back <=39367. Perfect! This summed all hours that occured before or on 12-Oct-07. But when I try an inclusive range, AND("="&C4,"<="&E4) in the criteria, the date values are correct when I Evaluate Formula, but this expression using AND comes back #VALUE. I'm puzzled. "Shane Devenshire" wrote: Hi, For the first formula you can use =SUMIFS('FY08'!F:F,'FY08'!D:D,"="&C1,'FY08'!D:D," <="&D1) I've changed to C1 and D1 and the dates are all entered as dates. If this helps, please click the Yes button, Cheers, Shane Devenshire "Richard Manor" wrote: Workbook to report all OT (overtime) performed during FY08 within a range of date for comparison to current FY usage. Two worksheets, 'Report' and 'FY08'. 'FY08'!D:D contains the dates the OT was performed and 'FY08'F:F contains the number of hours performed for that date. Each row is another occurrence of OT performed. Worksheet 'Report' each row holds the two dates for the criteria, in column C (start date) and column E (end date), ie, 10/8/2007 and 10/12/2007. Equation for each row for sum of hours performed within date range. Problem: At home I have Excel 2007 and Excel 2003 at work. Solved the equation in Excel 2007 as follows: =sumifs('FY08'!F:F,'FY08'!D:D,"="&text(C2,"mm/dd/yyyy"),'FY08'!D:D,"<="&text(E2,""mm/dd/yyyy"). If there is a simpler way I'd love to see it. Back to the problem- At work, Excel 2003 there is no SUMIFS function. Can not get it to work. Tried SUMIF as follows: =sumif('FY08'!D:D,(and(('FY08'!DD,"="&text(C2,"mm/dd/yyyy")),('FY08'!D:D."<="&text(E2,"mm/dd/yyyy")))),'FY08'!F:F) Tried CTRL+SHIFT+ENTER, still no joy. Your suggestions please. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumifs, sumif with dates
SUMPRODUCT ?? that's multipling then adding an array isn't it? I would need
this spelled out for me. "Shane Devenshire" wrote: Hi, In 2003 use =SUMPRODUCT(--(D:D=C1),--(D:D<=D1),F:F) Change the references as needed Cheers, Shane Devenshire "Richard Manor" wrote: Workbook to report all OT (overtime) performed during FY08 within a range of date for comparison to current FY usage. Two worksheets, 'Report' and 'FY08'. 'FY08'!D:D contains the dates the OT was performed and 'FY08'F:F contains the number of hours performed for that date. Each row is another occurrence of OT performed. Worksheet 'Report' each row holds the two dates for the criteria, in column C (start date) and column E (end date), ie, 10/8/2007 and 10/12/2007. Equation for each row for sum of hours performed within date range. Problem: At home I have Excel 2007 and Excel 2003 at work. Solved the equation in Excel 2007 as follows: =sumifs('FY08'!F:F,'FY08'!D:D,"="&text(C2,"mm/dd/yyyy"),'FY08'!D:D,"<="&text(E2,""mm/dd/yyyy"). If there is a simpler way I'd love to see it. Back to the problem- At work, Excel 2003 there is no SUMIFS function. Can not get it to work. Tried SUMIF as follows: =sumif('FY08'!D:D,(and(('FY08'!DD,"="&text(C2,"mm/dd/yyyy")),('FY08'!D:D."<="&text(E2,"mm/dd/yyyy")))),'FY08'!F:F) Tried CTRL+SHIFT+ENTER, still no joy. Your suggestions please. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumifs, sumif with dates
Hi,
The basic idea D:D=C1 Compares the date in cell C1 with all the cells in the range, here all of column D, it return an array (collection) of TRUE and FALSE answers. using -- forces Excel to convert TRUE to 1 and FALSE to 0. Same thing for D:D<=E1 maybe I should have used E1 so it doesn't look like its in the range you are checking. After the -- you have a collection of 1,0,11,0,0,... F:F is just all the values, again you adjust these ranges to suit your situation. But all three ranges must be of equal size. Now SUMPRODUCT multiples each element of the the three arrays, think 1 1 3456 0 1 546 0 0 231 1 1 8971 1*1 = 1 so if both date conditions were met Excel multiplies 1*1*3456 0*1, 1*0 and 0*0 all return 0 and 0*546 is 0 Then the SUMPRODUCT does its sum thing and adds up all the results. which would be something like 3456 0 0 0 .... If this helps, please click the Yes button, Cheers, Shane Devenshire "Richard Manor" wrote: SUMPRODUCT ?? that's multipling then adding an array isn't it? I would need this spelled out for me. "Shane Devenshire" wrote: Hi, In 2003 use =SUMPRODUCT(--(D:D=C1),--(D:D<=D1),F:F) Change the references as needed Cheers, Shane Devenshire "Richard Manor" wrote: Workbook to report all OT (overtime) performed during FY08 within a range of date for comparison to current FY usage. Two worksheets, 'Report' and 'FY08'. 'FY08'!D:D contains the dates the OT was performed and 'FY08'F:F contains the number of hours performed for that date. Each row is another occurrence of OT performed. Worksheet 'Report' each row holds the two dates for the criteria, in column C (start date) and column E (end date), ie, 10/8/2007 and 10/12/2007. Equation for each row for sum of hours performed within date range. Problem: At home I have Excel 2007 and Excel 2003 at work. Solved the equation in Excel 2007 as follows: =sumifs('FY08'!F:F,'FY08'!D:D,"="&text(C2,"mm/dd/yyyy"),'FY08'!D:D,"<="&text(E2,""mm/dd/yyyy"). If there is a simpler way I'd love to see it. Back to the problem- At work, Excel 2003 there is no SUMIFS function. Can not get it to work. Tried SUMIF as follows: =sumif('FY08'!D:D,(and(('FY08'!DD,"="&text(C2,"mm/dd/yyyy")),('FY08'!D:D."<="&text(E2,"mm/dd/yyyy")))),'FY08'!F:F) Tried CTRL+SHIFT+ENTER, still no joy. Your suggestions please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIFS formula using dates and text as criteria | Excel Discussion (Misc queries) | |||
SUMIF and SUMIFS | Excel Worksheet Functions | |||
SUMIF or SUMIFS | Excel Worksheet Functions | |||
SUMIFS with dates | Excel Worksheet Functions | |||
Sumif & Dates | Excel Worksheet Functions |