Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default sumifs, sumif with dates

Try

=SUMIF(D:D,"="&text(C2,"mm/dd/yyyy"),F:F)-SUMIF(D:D,""&text(E2,"mm/dd/yyyy"),F:F)

although SUMIF doesn't differ between text numbers and numbers in this case
you should really
replace this


text(C2,"mm/dd/yyyy")


with


this

=DATE(YEAR(C2),MONTH(C2),DAY(C2))


other functions are not as forgiving.



--


Regards,


Peo Sjoblom

"Richard Manor" <Richard wrote in message
...
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMIFS formula using dates and text as criteria Trish Excel Discussion (Misc queries) 4 May 21st 23 07:46 PM
SUMIF and SUMIFS Colts41 Excel Worksheet Functions 0 August 27th 08 06:11 PM
SUMIF or SUMIFS Jeff[_10_] Excel Worksheet Functions 4 January 30th 08 08:11 AM
SUMIFS with dates Chinni Krishna Reddy[_2_] Excel Worksheet Functions 2 April 16th 07 10:02 PM
Sumif & Dates angela Excel Worksheet Functions 4 May 17th 06 07:30 PM


All times are GMT +1. The time now is 12:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"