# Bring back value based on date

1. ## Bring back value based on date

Hello all,
I'm trying to help someone out with a formula to bring back a value based on a certain company and a certain date.
sheet 1 is where im trying to bring back value to its certain date.
project invoice log is where all the information is located.
example:
project invoice log:
Sage Environmental(I5)- \$8093.10(M5) - 03/25/10(O5)
sheet 1
Sage Environmental(A3) - \$8093.10(AO3)

Hope I explained it enough.
mrggutz

I have attached a sample workbook

2. ## Re: Bring back value based on date

I'm not sure exactly what you want, but if I'm right your workbook layout and proposed method will be cumbersome. I suggest you look at how your source data is laid out & consider using a PivotTable

3. ## Re: Bring back value based on date

I was trying to use something like this but its not working.
``Please Login or Register  to view this content.``

4. ## Re: Bring back value based on date

Assuming that you are (both) working in Excel 2003, the SUMPRODUCT function cannot handle full columns. Excel 2007 can but it's not recommended.

So, first step would be to change I:I to, say, I5:I1000. Similarly, U:U and M:M. The "–" should be "--" without quotes.

The formula would look something like this:

``Please Login or Register  to view this content.``

As RoyUK has said, it looks as though it may be difficult to generalise this.

Regards

5. ## Re: Bring back value based on date

I tried that and it just brought back a "0" value. I will keep working on it
thx

6. ## Re: Bring back value based on date

If you tried it, why wasn't that in the sample you uploaded?

Where do you intend to put the formula?

I put this in cell W5: [CODE=SUMPRODUCT(--(I5:I1000="Sage Environmental"),--(U5:U1000=U5),(M5:M1000)) [/CODE]

and dragged down; these were the results in W5 to W10

HTML Code:
``````8093.1
17964.69
20101.83
20101.83
10488.44
7746.04``````

Regards

7. ## Re: Bring back value based on date

This worked in 2007 version using "Sumifs" but I know it will not work in 2003 version, what can I use instead in 03?
``Please Login or Register  to view this content.``

Sumproduct

9. ## Re: Bring back value based on date

I note that in your SUMIFS, you are comparing something in A2 to column I.

In the sample, A2 has a heading, A3 has OC-08 and column I has the Vendor names.

We may, therefore, be talking at cross purposes.

I'm sure that's as frustrating for you as it is for me.

Regards

10. ## Re: Bring back value based on date

I just tried it this morning at home, the sample is from yesterday sry.
I plan to use the formula in sheet 1 under it appropriate date.
I'm gonna try what you did and I will let you know how it comes out thx.

11. ## Re: Bring back value based on date

In cell C3 on Sheet1 (in your sample workbook)

``Please Login or Register  to view this content.``

returns

HTML Code:
``20101.83``

I think this demonstrate the principle; you need to adjust the ranges and search values accordingly.

Regards

12. ## Re: Bring back value based on date

Thank you very much that works I will adjust where needed.
thanks again have a good one.

13. ## Re: Bring back value based on date

You're welcome. Thanks for the feedback.

14. ## Re: Bring back value based on date

Hey no problem I got it all working great now.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1