DATE RANGE FORMULA

[roy.okinawa]

I use this formula when I want total number from another worksheet to a
target worksheet:

=SUMPRODUCT(--(TEXT(Overall!H8:H2100,"mmm/yyyy")=TEXT(I1,"mmm/yyyy")),Overall!AI8:AI2100)

Is there a formula I can use with a date range that will pull data across to
another worksheet (target worksheet?) Example of what target worksheet would
look like:

Date: 7/10/2009

WO# DATE RCVD COST REMARKS

Thanks.

 

[Shane Devenshire]

Hi,

Are you asking about pulling over data based on a data range or are you
asking how to bring over data that is not numeric?

=SUMPRODUCT(--(TEXT(Overall!H8:H2100,"mmm/yyyy")=TEXT(I1,"mmm/yyyy")),Overall!AI8:AI2100)

Another question about the above formula, why are you converting it to text
dates? since both components seem to be dates why not just leave them that
way.

=SUMPRODUCT(--(Overall!H8:H2100>=A1),--(Overall!H8:H2100<=A2),Overall!AI8:AI2100)

Where A1 and A2 contain dates such as 1/1/2009 and 12/31/2009.

 

[Jacob Skaria]

With Date in cell A1 of your current sheet and your data in 'Target' (row 1
with headers); the below formula will give you the total for the month of
date mentioned in cell B1

=SUMPRODUCT(--(TEXT(Target!B2:B100,"mmm/yyyy")=TEXT(A1,"mmm/yyyy")),Target!C2:C100)


If this post helps click Yes

 

[roy.okinawa]

Hello,

Yes. Based on the date range I want to bring info from another worksheet to
another worksheet. I don't not want to SUMPRODUCT, I want snapshot data info
for a specific month/year.

Here is an example (all this info is pulled from master worksheet based on
date range):

Date: 7/2009

WO# DATE RCVD STATUS DATE CLOSED REMARKS
DV1 7/1/2009 Open 7/5/2009
in-progress
DV2 7/3/2009 Open 7/7/2009
in-progress
DV3 7/9/2009 Closed 7/9/2009
pending parts
DV4 7/10/2009 Inbound
completed

 

[roy.okinawa]

Hi again,

Wasn't aware I was doing that on converting dates to text (as far as I know,
I know it works though.) This formula just gives me totals.