sumifs, sumif with dates

[Richard Manor]

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 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,">="&text(C2,"mm/dd/yyyy"),'FY08'!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,(and(('FY08'!DD,">="&text(C2,"mm/dd/yyyy")),('FY08'!D."<="&text(E2,"mm/dd/yyyy")))),'FY08'!F:F) Tried CTRL+SHIFT+ENTER, still no joy.

Your suggestions please.

 

[Shane Devenshire]

Hi,

For the first formula you can use

=SUMIFS('FY08'!F:F,'FY08'!D,">="&C1,'FY08'!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

 

[Shane Devenshire]

Hi,

In 2003 use

=SUMPRODUCT(--(D>=C1),--(D<=D1),F:F)

Change the references as needed

Cheers,
Shane Devenshire

 

[Peo Sjoblom]

Try

=SUMIF(D,">="&text(C2,"mm/dd/yyyy"),F:F)-SUMIF(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]

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,"<="&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.

 

[Richard Manor]

SUMPRODUCT ?? that's multipling then adding an array isn't it? I would need
this spelled out for me.

 

[Shane Devenshire]

Hi,

The basic idea
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<=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