sumif between dates (quarters)

[Guest]

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.

 

[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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Yes there

You might consider:

=date(2007,7,8)

or

=DATEVALUE("07/08/2007")

 

[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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Yes there

You might consider:

=date(2007,7,8)

or

=DATEVALUE("07/08/2007")

 

[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

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

 

[Harlan Grove]

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)