Results 1 to 15 of 18
Thread: lookup and sum formula (2003)

20070618, 22:31 #1
 Join Date
 Jun 2007
 Location
 Salem, Oregon
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!

20070618, 22:38 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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?

20070618, 22:51 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: lookup and sum formula (2003)
Perhaps the links in Re: SUMIF with multiple criteria (Excel 2003) could be of use
Steve

20070618, 22:55 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>Legare Coleman

20070618, 23:07 #5
 Join Date
 Jun 2007
 Location
 Salem, Oregon
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20070618, 23:15 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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).

20070618, 23:15 #7
 Join Date
 Jun 2007
 Location
 Salem, Oregon
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20070618, 23:24 #8
 Join Date
 Jun 2007
 Location
 Salem, Oregon
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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).

20070618, 23:45 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20070618, 23:53 #10
 Join Date
 Jun 2007
 Location
 Salem, Oregon
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: lookup and sum formula (2003)
I figured that also after I emailed you.
Thanks a lot!

20070618, 23:58 #11
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: lookup and sum formula (2003)
I don't think you need any VLOOKUP. Does the formula in the attached do what you want?
Legare Coleman

20070619, 21:37 #12
 Join Date
 Jun 2007
 Location
 Salem, Oregon
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20070619, 21:42 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20070619, 21:46 #14
 Join Date
 Jun 2007
 Location
 Salem, Oregon
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: lookup and sum formula (2003)
Yup! That works! Thanks Hans!

20070619, 22:31 #15
 Join Date
 Jun 2007
 Location
 Salem, Oregon
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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)))