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.