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 with date range
 

=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" wrote in message
...
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 with date range
 

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" wrote in message
...
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 with date range
 

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" wrote in message
...
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" wrote in message
...
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 with date range
 

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" wrote in message
...
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" wrote in message
...
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 with date range
 

=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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=571351

Sumproduct with date range
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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