I am wanting to sumif a series of date times in a colum and sum another colum
with $ values
eg
A B
21-1-2008 07:30 $500
26-1-2009 04:00 $200
Wanting to sum if the datetime field relates to "2009"

One way:

=SUMPRODUCT(--(YEAR(A1:A100)=2009)*B1:B100)
Adjust the range to suit but make sure both ranges are exactly the same no
of rows!
You could also make the 2009 part a reference to a cell in which you enter
the year for ease sake; eg
=SUMPRODUCT(--(YEAR(A1:A100)=C1)*B1:B100) Then you can enter 2009 or 2008,
etc in cell C1.

Rob

"JMB" wrote in message
...
I am wanting to sumif a series of date times in a colum and sum another
colum
with $ values
eg
A B
21-1-2008 07:30 $500
26-1-2009 04:00 $200
Wanting to sum if the datetime field relates to "2009"

If the dates/times are true Excel dates/times:

=SUMPRODUCT(--(YEAR(A1:A10)=2009),B1:B10)

If the dates/times are text strings:

=SUMPRODUCT(--(ISNUMBER(FIND(2009,A1:A10))),B1:B10)


--
Biff
Microsoft Excel MVP


"JMB" wrote in message
...
I am wanting to sumif a series of date times in a colum and sum another
colum
with $ values
eg
A B
21-1-2008 07:30 $500
26-1-2009 04:00 $200
Wanting to sum if the datetime field relates to "2009"

One way: =SUMPRODUCT((YEAR(A2:A10)=2009)*B2:B10)
which assumes real date/times in col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JMB" wrote:
I am wanting to sumif a series of date times in a colum and sum another colum
with $ values
eg
A B
21-1-2008 07:30 $500
26-1-2009 04:00 $200
Wanting to sum if the datetime field relates to "2009"

Think the double minus "--" is not necessary if you're multiplying it direct
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

That works fine, is there also a way that you could do the same thing only
monthly by year.

"Max" wrote:

One way: =SUMPRODUCT((YEAR(A2:A10)=2009)*B2:B10)
which assumes real date/times in col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JMB" wrote:
I am wanting to sumif a series of date times in a colum and sum another colum
with $ values
eg
A B
21-1-2008 07:30 $500
26-1-2009 04:00 $200
Wanting to sum if the datetime field relates to "2009"

"JMB" wrote:
That works fine, is there also a way that you could
do the same thing only monthly by year.

Sure, something like this:
=SUMPRODUCT((TEXT(A2:A10,"mmmyyyy")="Jan2009")*B2: B10)

Do take a moment to press the "Yes" button below to the question:
"Was this post helpful to you?" from where you're reading this.
It'll ensure a longer shelf life to this thread for the general benefit
of other readers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---