Sumif a date time colum and information

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JMB - 20 Mar 2008 02:13 GMT

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"

Reply to this Message

RobN - 20 Mar 2008 02:38 GMT

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

>I am wanting to sumif a series of date times in a colum and sum another
>colum
[quoted text clipped - 4 lines]
> 26-1-2009 04:00 $200
> Wanting to sum if the datetime field relates to "2009"

Reply to this Message

Max - 20 Mar 2008 03:21 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

T. Valko - 20 Mar 2008 02:40 GMT

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)

Signature

Biff
Microsoft Excel MVP

>I am wanting to sumif a series of date times in a colum and sum another
>colum
[quoted text clipped - 4 lines]
> 26-1-2009 04:00 $200
> Wanting to sum if the datetime field relates to "2009"

Reply to this Message

Max - 20 Mar 2008 02:46 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I am wanting to sumif a series of date times in a colum and sum another colum
> with $ values
[quoted text clipped - 3 lines]
> 26-1-2009 04:00 $200
> Wanting to sum if the datetime field relates to "2009"

Reply to this Message

JMB - 20 Mar 2008 05:16 GMT

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

> One way: =SUMPRODUCT((YEAR(A2:A10)=2009)*B2:B10)
> which assumes real date/times in col A
[quoted text clipped - 5 lines]
> > 26-1-2009 04:00 $200
> > Wanting to sum if the datetime field relates to "2009"

Reply to this Message

Max - 20 Mar 2008 07:34 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message