# Thread: lookup and sum formula (2003)

1. ## lookup and sum formula (2003)

I was wondering if It is possible to create a formula that can lookup values (on the different spreadsheet) and add those values that meet date criteria in the same formula. I was trying to combine VLOOKUP (lookup name is right above the column with values) and SUMIF formulas. However, I was unsuccessfull so far. If someone knows how to do that, please help!

2. ## Re: lookup and sum formula (2003)

Welcome to Woody's Lounge!

Could you explain in more detail what you want to accomplish, and perhaps attach a small sample workbook that illustrates it?

3. ## Re: lookup and sum formula (2003)

Perhaps the links in Re: SUMIF with multiple criteria (Excel 2003) could be of use

Steve

4. ## Re: lookup and sum formula (2003)

If I understand what you are asking, then assume the following:

The dates are in A1:A30
The values to be summed are in B1:B30
If you want to sum all of the values in B1:B30 where the date in A1:A30 is greater than or equal to the date in F1 and less than or equal to the date in G1, then you could use the following formula:

<code>
=SUMPRODUCT((A1:A30>=F1)*(A1:A30<=G1)*B1:B30)
</code>

5. ## Re: lookup and sum formula (2003)

I want to enter this formula into workbook Summary, cell G14. The data will come from woorkbook Q1, column G. The criteria is date in column E. In workbook Summary, cell G14, I want to have total of all SMI debt for the properties acquired prior to Jan 2002. See attached spreadsheet.

6. ## Re: lookup and sum formula (2003)

Enter the date 1/1/2002 in (for example) cell G1 on the Q1 sheet. The formula
<code>
=SUMIF('Q1'!E7:E22,"<"&'Q1'!G1,'Q1'!G7:G22)
</code>
will add the values in G7:G22 for which the date in E7:E22 is before 1/1/2002 (or whichever date is entered in G1).

7. ## Re: lookup and sum formula (2003)

This is a useful formula that I can use instead of SUMIF. My only problem is that I can figure out how to factor it correctly into the Vlookup or other lookup formula.

alex

8. ## Re: lookup and sum formula (2003)

Thanks Hans!

Is there a way that this formula can lookup a date in column E? I can't enter this date up at the top (the dates has to stay in column E).

9. ## Re: lookup and sum formula (2003)

The formula I proposed does look up dates in column E, but you have to specify the value to compare with (January 1, 2002 in your example) somewhere. It doesn't have to be cell G1 on the Q1 sheet.

10. ## Re: lookup and sum formula (2003)

I figured that also after I emailed you.

Thanks a lot!

11. ## Re: lookup and sum formula (2003)

I don't think you need any VLOOKUP. Does the formula in the attached do what you want?

12. ## Re: lookup and sum formula (2003)

Thanks! This exactly what I needed: date criteria in the same formula without reference to other cells. I am working this formula that you've send me earlier =SUMPRODUCT(('Q1'!E9:E254>=DATE(2002,1,1))*('Q1'!E 9:E254<=DATE(2002,12,31))*G9:G254) (My range is different in here than in the example I've attached, but it should work anyway). I am missing something in this formula and I can't figure it out. Can you help me?

13. ## Re: lookup and sum formula (2003)

I think you want
<code>
=SUMPRODUCT(('Q1'!E9:E254>=DATE(2002,1,1))*('Q1'!E 9:E254<=DATE(2002,12,31))*'Q1'!G9:G254)
</code>
I added 'Q1'! to the last range.

14. ## Re: lookup and sum formula (2003)

Yup! That works! Thanks Hans!

15. ## Re: lookup and sum formula (2003)

I am trying to get # of faciliteis opened prior to 1/1/2002. What is wrong with this formula? =COUNTIF('Q1'!A9:A254,'Q1'!E9:E254<DATE(2002,1,1)) it returns zero.

How do I also get a rage of dates in criteria. It should look something like this =COUNTIF('Q1'!A9:A254,('Q1'!E9:E254>=DATE(2002,1,1 ))*'(Q1'!E9:E254<=DATE(2002,12,31)))

Page 1 of 2 12 Last

#### Posting Permissions

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