Sumproduct with date range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ermeko - 14 Aug 2006 13:35 GMT

Hi,
I want to count an "A" column where the column "B" is between a given date.
The date in column B has a date and a time("dd-mm-yyyy hh:mm:ss"). The date
arguments in the sumproduct are in the "dd-mm-yyyy" form. The formula should
find "like products", not the exact match.
Thank you,
ermeko

Reply to this Message

Bob Phillips - 14 Aug 2006 13:54 GMT

=SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200>=--"2006-08-01"
),--(B2:B200<=--"2006-08-31"))

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi,
> I want to count an "A" column where the column "B" is between a given date.
[quoted text clipped - 3 lines]
> Thank you,
> ermeko

Reply to this Message

ermeko - 14 Aug 2006 15:04 GMT

Thanks,
=SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200>=--"2006-08-01"

> ),--(B2:B200<=--"2006-08-01")) gives zero but there are dates like 2006-08-01 23:02:00, 2006-08-01 22:00:00 in B column.

When the begining and ending date is the same it does nor sum the quantity.

> =SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200>=--"2006-08-01"
> ),--(B2:B200<=--"2006-08-31"))
[quoted text clipped - 9 lines]
> > Thank you,
> > ermeko

Reply to this Message

Bob Phillips - 14 Aug 2006 17:15 GMT

what is the product name and what value are you testing for (My formula was
just an example).

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Thanks,

=SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200>=--"2006-08-01"

> > ),--(B2:B200<=--"2006-08-01")) gives zero but there are dates like 2006-08-01 23:02:00, 2006-08-01 22:00:00 in B column.
> When the begining and ending date is the same it does nor sum the quantity.

=SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200>=--"2006-08-01"

> > ),--(B2:B200<=--"2006-08-31"))
> >
[quoted text clipped - 8 lines]
> > > Thank you,
> > > ermeko

Reply to this Message

ermeko - 16 Aug 2006 12:44 GMT

Hi,
the formula is a bit different:
=SUMPRODUCT(--(b2:B200>=--"2006-08-01"),--(B2:B200<=--"2006-08-01"),--(B2:B200<=--"2006-08-01"),--(c2:c200="apple"),a2:a200)
C is a column with product names.
when I change formula to:
=SUMPRODUCT(--(b2:B200>=--"2006-08-01"),--(B2:B200<=--"2006-08-03"),--(B2:B200<=--"2006-08-01"),--(c2:c200="apple"),a2:a200)
it works but it but when I want the sum for only one day it does not work.

Thank you

> what is the product name and what value are you testing for (My formula was
> just an example).
[quoted text clipped - 21 lines]
> > > > Thank you,
> > > > ermeko

Reply to this Message

Dav - 16 Aug 2006 14:44 GMT

=SUMPRODUCT(--(b2:B200>=--"2006-08-01"),--(B2:B200<=--"2006-08-03"),--(B2:B200<=--"2006-08-01"),--(c2:c200="apple"),a2:a200)
it works but it but when I want the sum for only one day it does not
work.

I am a little confused, what do the 2nd and 3rd term do? if b2:b200 <
2006-08-01 it will also have to be less than 2006-08-03

Also as it is written it will only select values in column B if they
equal 2006-08-01. If any times are also included for a given day, it
will not be selected, would would have to add 1 to the day you were
interssted in to account for this.

or am I missing something

Regards

Dav

Signature

Dav

Reply to this Message

Bob Phillips - 16 Aug 2006 17:17 GMT

You don't need to test <= twice.

This works for me with just a single day

=SUMPRODUCT(--(B2:B200>=--"2006-08-01"),--(B2:B200<=--"2006-08-01"),--(C2:C2
00="apple"),A2:A200)

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi,
> the formula is a bit different:

=SUMPRODUCT(--(b2:B200>=--"2006-08-01"),--(B2:B200<=--"2006-08-01"),--(B2:B2
00<=--"2006-08-01"),--(c2:c200="apple"),a2:a200)

> C is a column with product names.
> when I change formula to:

=SUMPRODUCT(--(b2:B200>=--"2006-08-01"),--(B2:B200<=--"2006-08-03"),--(B2:B2
00<=--"2006-08-01"),--(c2:c200="apple"),a2:a200)

> it works but it but when I want the sum for only one day it does not work.
>
[quoted text clipped - 4 lines]
> >
> > > Thanks,

=SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200>=--"2006-08-01"

> > > > ),--(B2:B200<=--"2006-08-01")) gives zero but there are dates like
> > 2006-08-01 23:02:00, 2006-08-01 22:00:00 in B column.
> > > When the begining and ending date is the same it does nor sum the
> > quantity.

=SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200>=--"2006-08-01"

> > > > ),--(B2:B200<=--"2006-08-31"))
> > > >
[quoted text clipped - 9 lines]
> > > > > Thank you,
> > > > > ermeko

Reply to this Message