Sumif a date time colum and information

[JMB]

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"

 

[RobN]

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

 

[T. Valko]

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)

 

[Max]

One way: =SUMPRODUCT((YEAR(A2:A10)=2009)*B2:B10)
which assumes real date/times in col A

 

[Max]

Think the double minus "--" is not necessary if you're multiplying it direct

 

[JMB]

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

 

[Max]

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.