Sumproduct with date range

[Guest]

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

 

[Bob Phillips]

=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)

 

[Guest]

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]

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)

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

2006-08-01 23:02:00, 2006-08-01 22:00:00 in B column.

 

[Guest]

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

 

[Dav]

=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

 

[Bob Phillips]

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)