SumProd from 2 columns and date range

[roy.okinawa]

I have looked through a few answers here but can't find my solution. I keep
getting Value# or a wrong total.

I want to sumproduct from 2 columns when meeting select criteria and dates.
I use this for now for only one date (i.e. Oct 2009)

=SUMPRODUCT(--(TEXT(Overall!H8:H3000,"mmm/yyyy")=TEXT(A1,"mmm/yyyy")),--(ISNUMBER(SEARCH(A2,Overall!F8:F3000))))

I want to be able to enter Jan 09 in A1 and Oct 09 in B2 and have that
search and sum total for A2 criteria. All months between Jan/Oct are
included. Date ranges (A1, B2) can change, as well as criteria (A2.)

Thanks.

 

[Max]

Untested, but something like should work:
=SUMPRODUCT((Overall!H8:H3000>=A1)*(Overall!H8:H3000<B2)*(ISNUMBER(SEARCH(A2,Overall!F8:F3000))))

where you would input in
A1 = StartDate in full, eg enter: 1 Jan 2009
B2 = "EndDate" which is the 1st of the next month, eg enter: 1 Nov 2009 (if
you want to capture it till end Oct 09). The operator "<" in "<B2" is
intentional. If you do it like this for the enddate, you don't have to worry
which date is the last date of any month/yr

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Fred Smith]

The problem is that when you enter "Jan 09" in A1, that's not a date to
Excel, it's text.

So enter a true date, like 1/1/09, into your cell, then your formula will
work.

Regards,
Fred.

 

[Max]

The problem is ...
Think Roy also wanted a date range criteria applied, between a certain
startdate to enddate, which required a different handle. His posted formula
traps for dates falling within a certain month/yr,
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[roy.okinawa]

Max,

Yes I want it for certain date ranges. In my OP I put Oct 09, etc. I meant
to use 1/1/09. So my input cells would be (all dates that fall between that
criteria need to be totaled:

A1: 1/1/2009
A2: 10/1/2009
A6: 54976

The above date criteria (A1, A2) and part number (A6) are looking for the
total count of the part number that matches against Overall worksheet columns
H (date)and F (part number).

I tried your formula but it totals for some: 3 years worth instead of
current year 2009 dates and no totals for others.

Thanks for the assistance.

 

[Max]

A1: 1/1/2009

A2: 10/1/2009
A6: 54976

You need to amend the enddate in A2 to read as: 11/1/2009 (ie the 1st of the
"next" month), then use this:
=SUMPRODUCT((Overall!H8:H3000>=A1)*(Overall!H8:H3000<A2)*(ISNUMBER(SEARCH(A6,Overall!F8:F3000))))
Above will apply the date range criteria of dates from 1 Jan 2009 till end
Oct 2009.
(The "<" in "<A2" is intentional)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[roy.okinawa]

That did it.

Thanks for the help.

You need to amend the enddate in A2 to read as: 11/1/2009 (ie the 1st of the
"next" month), then use this:
=SUMPRODUCT((Overall!H8:H3000>=A1)*(Overall!H8:H3000<A2)*(ISNUMBER(SEARCH(A6,Overall!F8:F3000))))
Above will apply the date range criteria of dates from 1 Jan 2009 till end
Oct 2009.
(The "<" in "<A2" is intentional)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---



.