Thread: Sumproduct with date range

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

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

--
HTH

Bob Phillips

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

"ermeko" <ermeko@discussions.microsoft.com> wrote in message
news:08798E05-66A6-4F50-B387-B0D4573CEF2D@microsoft.com...
> 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

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.



"Bob Phillips" wrote:

> =SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200>=--"2006-08-01"
> ),--(B2:B200<=--"2006-08-31"))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> news:08798E05-66A6-4F50-B387-B0D4573CEF2D@microsoft.com...
> > 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
>
>
>

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

--
HTH

Bob Phillips

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

"ermeko" <ermeko@discussions.microsoft.com> wrote in message
news:58932585-46AE-43A1-A35D-7E42AFDD0B49@microsoft.com...
> 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.
>
>
>
> "Bob Phillips" wrote:
>
> >
=SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200>=--"2006-08-01"
> > ),--(B2:B200<=--"2006-08-31"))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> > news:08798E05-66A6-4F50-B387-B0D4573CEF2D@microsoft.com...
> > > 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
> >
> >
> >

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


"Bob Phillips" wrote:

> what is the product name and what value are you testing for (My formula was
> just an example).
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> news:58932585-46AE-43A1-A35D-7E42AFDD0B49@microsoft.com...
> > 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.
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > >
> =SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200>=--"2006-08-01"
> > > ),--(B2:B200<=--"2006-08-31"))
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> > > news:08798E05-66A6-4F50-B387-B0D4573CEF2D@microsoft.com...
> > > > 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
> > >
> > >
> > >
>
>
>

=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

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)

--
HTH

Bob Phillips

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

"ermeko" <ermeko@discussions.microsoft.com> wrote in message
news:D166A789-0EA6-4B05-A8F6-6A8A18CBFAA0@microsoft.com...
> 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.
>
> Thank you
>
>
> "Bob Phillips" wrote:
>
> > what is the product name and what value are you testing for (My formula
was
> > just an example).
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> > news:58932585-46AE-43A1-A35D-7E42AFDD0B49@microsoft.com...
> > > 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.
> > >
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > >
> >
=SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200>=--"2006-08-01"
> > > > ),--(B2:B200<=--"2006-08-31"))
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> > > > news:08798E05-66A6-4F50-B387-B0D4573CEF2D@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >