Hi,
I'm trying to sum hours spent in each quarter. Am using xl 03 and a 7/12
date format. Tried 3/13*<7/1 for 2nd quarter criteria, but it doesn't work.
No formula error statement.
Thanks.
--
Gary

Assuming that you mean the date system is mm/dd and you want a formula which
is not year dependant then try:

=SUMPRODUCT((MONTH(B2:B25)=3)*(DAY(B2:B25)=13)*( MONTH(B2:B25)<7)*C2:C25)

with the list of dates in B2:B25 and the amounts in C2:C25

If you can have the year included then use:

=SUMPRODUCT((B2:B25D1)*(B2:B25<E1)*C2:C25)
with the last day of the 1st quarter in D1 and the first day of the 3rd
quarter in E1


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"island hopper" wrote in message
...
Hi,
I'm trying to sum hours spent in each quarter. Am using xl 03 and a 7/12
date format. Tried 3/13*<7/1 for 2nd quarter criteria, but it doesn't
work.
No formula error statement.
Thanks.
--
Gary

Thanks Sandy from Scotland.
I can put the year in the date. Is there any way to use a "hard" date, e.g.
3/31/07
rather than point to a cell with that date in it?
Thanks, Gary (from Colorado).
--
Gary


"Sandy Mann" wrote:

Assuming that you mean the date system is mm/dd and you want a formula which
is not year dependant then try:

=SUMPRODUCT((MONTH(B2:B25)=3)*(DAY(B2:B25)=13)*( MONTH(B2:B25)<7)*C2:C25)

with the list of dates in B2:B25 and the amounts in C2:C25

If you can have the year included then use:

=SUMPRODUCT((B2:B25D1)*(B2:B25<E1)*C2:C25)
with the last day of the 1st quarter in D1 and the first day of the 3rd
quarter in E1


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"island hopper" wrote in message
...
Hi,
I'm trying to sum hours spent in each quarter. Am using xl 03 and a 7/12
date format. Tried 3/13*<7/1 for 2nd quarter criteria, but it doesn't
work.
No formula error statement.
Thanks.
--
Gary

Yes there

You might consider:

=date(2007,7,8)

or

=DATEVALUE("07/08/2007")

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"island hopper" wrote:

Thanks Sandy from Scotland.
I can put the year in the date. Is there any way to use a "hard" date, e.g.
3/31/07
rather than point to a cell with that date in it?
Thanks, Gary (from Colorado).
--
Gary

Or even the enigmatic:

=SUMPRODUCT((B2:B25--("3/12/07"))*(B2:B25<--("7/1/07"))*C2:C25)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Wigi" wrote in message
...
Yes there

You might consider:

=date(2007,7,8)

or

=DATEVALUE("07/08/2007")

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"island hopper" wrote:

Thanks Sandy from Scotland.
I can put the year in the date. Is there any way to use a "hard" date,
e.g.
3/31/07
rather than point to a cell with that date in it?
Thanks, Gary (from Colorado).
--
Gary

Assuming your 1st quarter runs from January 1st to March 31st, 2nd quarter
from April 1st to June 30th, etc., you can use these formulas to get your
quarterly totals...

1st Q: =SUMPRODUCT((1=1+FLOOR((MONTH(B2:B25)-1)/3,1))*C2:C25)
2nd Q: =SUMPRODUCT((2=1+FLOOR((MONTH(B2:B25)-1)/3,1))*C2:C25)
3rd Q: =SUMPRODUCT((3=1+FLOOR((MONTH(B2:B25)-1)/3,1))*C2:C25)
4th Q: =SUMPRODUCT((4=1+FLOOR((MONTH(B2:B25)-1)/3,1))*C2:C25)

Notice the **only** difference between them is the number in front of the
equal sign (1 for first quarter, 2 for second quarter, etc.). Using these
will allow you look at all quarters at once. If, on the other hand, you only
need to look at one quarter at a time, then you can make do with this single
formula...

=SUMPRODUCT((A1=1+FLOOR((MONTH(B6:B1000)-1)/3,1))*C6:C1000)

where the quarter number is assumed to be placed in cell A1.

Rick


"island hopper" wrote in message
...
Thanks Sandy from Scotland.
I can put the year in the date. Is there any way to use a "hard" date,
e.g.
3/31/07
rather than point to a cell with that date in it?
Thanks, Gary (from Colorado).
--
Gary


"Sandy Mann" wrote:

Assuming that you mean the date system is mm/dd and you want a formula
which
is not year dependant then try:

=SUMPRODUCT((MONTH(B2:B25)=3)*(DAY(B2:B25)=13)*( MONTH(B2:B25)<7)*C2:C25)

with the list of dates in B2:B25 and the amounts in C2:C25

If you can have the year included then use:

=SUMPRODUCT((B2:B25D1)*(B2:B25<E1)*C2:C25)
with the last day of the 1st quarter in D1 and the first day of the 3rd
quarter in E1


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"island hopper" wrote in message
...
Hi,
I'm trying to sum hours spent in each quarter. Am using xl 03 and a
7/12
date format. Tried 3/13*<7/1 for 2nd quarter criteria, but it doesn't
work.
No formula error statement.
Thanks.
--
Gary

"island hopper" wrote...
I'm trying to sum hours spent in each quarter. Am using xl 03 and a 7/12
date format. Tried 3/13*<7/1 for 2nd quarter criteria, but it doesn't
work.

Presumably 3/13 is a typo and should have been 3/31. If so,

=SUMIF(Dates,"3/31/2007",Values)-SUMIF(Dates,"6/30/2007",Values)