sumif between dates (quarters)

I

island hopper

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.
 
S

Sandy Mann

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:B25>D1)*(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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
I

island hopper

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 said:
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:B25>D1)*(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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
S

Sandy Mann

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk


Wigi said:
Yes there

You might consider:

=date(2007,7,8)

or

=DATEVALUE("07/08/2007")
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

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 said:
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).
Click to expand...
 
H

Harlan Grove

island hopper said:
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.
Click to expand...

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)
 
Top