formula help PLEASE PT 2
in [General]
|
Prev: missing styles
Next: Appointments won't open
From: Norm on 10 Apr 2010 16:25 I need to get the information from sheet 2 row 16 using the date in sheet 1 C4 and the same date in sheet 2 A16 sheet 1 REPORT DATE 4/12/2010 APR 11 - 18 KPI No Genesis KPI's UOM sheet2 15-Apr 16-Apr 17-Apr Apr11-17 11 19 10 18-Apr 19-Apr or can I lookup a sum of cells using a range of dates? Thanks
From: Don Guillett on 10 Apr 2010 18:16 If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Norm" <Norm(a)discussions.microsoft.com> wrote in message news:EC885CD7-0440-48EE-A5DE-71597C8A33D5(a)microsoft.com... >I need to get the information from sheet 2 row 16 using the date in sheet 1 > C4 and the same date in sheet 2 A16 > sheet 1 > > REPORT DATE 4/12/2010 > APR 11 - 18 > KPI No Genesis KPI's UOM > > sheet2 > > 15-Apr > 16-Apr > 17-Apr > Apr11-17 11 19 10 > 18-Apr > 19-Apr > > or can I lookup a sum of cells using a range of dates? > Thanks >
From: JLatham on 10 Apr 2010 20:26 Norm, first - I think I provided a fix to the problem in the other discussion on VLOOKUP(). Check it out. Now, for this problem. If Row 16 on Sheet2 is the one where you have Apr11-17 shown in your example, that's probably not a real date and while you could use VLOOKUP() to return those values just as before, another way to get the values from a column on Sheet2 associated with dates would be like this. Assumptions: Sheet2 has dates in column A In sheet 1 you have the start date in C4 and the end of period date in D4 (could be any other cell, but D4 for this example). Then =SUMPRODUCT(--(Sheet2!$A:$A>=$C$4),--(Sheet2!$A:$A<=$D$4),--(Sheet2!B:B)) would give you the total of values in column B as a result. The way I've set up the formula, you can then drag it to the right on your sheet and the final column will change from B, to C, to D, to E, etc depending on how far across the sheet you drag or fill it. If you put 11-Apr in C4 and 17-Apr in D4, then it would give you the totals for that period. "Norm" wrote: > I need to get the information from sheet 2 row 16 using the date in sheet 1 > C4 and the same date in sheet 2 A16 > sheet 1 > > REPORT DATE 4/12/2010 > APR 11 - 18 > KPI No Genesis KPI's UOM > > sheet2 > > 15-Apr > 16-Apr > 17-Apr > Apr11-17 11 19 10 > 18-Apr > 19-Apr > > or can I lookup a sum of cells using a range of dates? > Thanks >
From: Norm on 11 Apr 2010 13:21 Thank you for the help on the other discussion it worked fine On this discussion I entered the formula and adjusted the "Sheet 2" to "OUTBOUND" to reflect the name on the sheet =SUMPRODUCT(--(OUTBOUND!$A:$A>=$C$4),--(OUTBOUND!$A:$A<=$D$4),--(OUTBOUND!B:B)) This results in a "#NUM in the cell What have I missed? "JLatham" wrote: > Norm, first - I think I provided a fix to the problem in the other discussion > on VLOOKUP(). Check it out. > > Now, for this problem. If Row 16 on Sheet2 is the one where you have > Apr11-17 shown in your example, that's probably not a real date and while you > could use VLOOKUP() to return those values just as before, another way to get > the values from a column on Sheet2 associated with dates would be like this. > > Assumptions: > Sheet2 has dates in column A > In sheet 1 you have the start date in C4 and the end of period date in D4 > (could be any other cell, but D4 for this example). Then > =SUMPRODUCT(--(Sheet2!$A:$A>=$C$4),--(Sheet2!$A:$A<=$D$4),--(Sheet2!B:B)) > would give you the total of values in column B as a result. The way I've > set up the formula, you can then drag it to the right on your sheet and the > final column will change from B, to C, to D, to E, etc depending on how far > across the sheet you drag or fill it. > If you put 11-Apr in C4 and 17-Apr in D4, then it would give you the totals > for that period. > > > "Norm" wrote: > > > I need to get the information from sheet 2 row 16 using the date in sheet 1 > > C4 and the same date in sheet 2 A16 > > sheet 1 > > > > REPORT DATE 4/12/2010 > > APR 11 - 18 > > KPI No Genesis KPI's UOM > > > > sheet2 > > > > 15-Apr > > 16-Apr > > 17-Apr > > Apr11-17 11 19 10 > > 18-Apr > > 19-Apr > > > > or can I lookup a sum of cells using a range of dates? > > Thanks > >
From: JLatham on 11 Apr 2010 20:51
I know you've seen this already in the other discussion, but I'll repeat here to 'close' this one: ===== Probably my fault, I was probably working in Excel 2007 when I built up the formula. You'll need to specify the start and end rows for the column references in OUTBOUND, like: =SUMPRODUCT(--(OUTBOUND!$A$1:$A$100>=Sheet1!$C$4),--(OUTBOUND!$A$1:$A$100<=Sheet1!$D$4),--(OUTBOUND!$B$1:$B$100)) You may want to increase the $A$100 and $B$100 row numbers to go well down below where the list ends now to keep from having to revise it for a while. In any case, that last row number needs to be the same for both the $A$### and $B$### references. My apologies for the headache. "Norm" wrote: > Thank you for the help on the other discussion it worked fine > On this discussion I entered the formula and adjusted the "Sheet 2" to > "OUTBOUND" to reflect the name on the sheet > > =SUMPRODUCT(--(OUTBOUND!$A:$A>=$C$4),--(OUTBOUND!$A:$A<=$D$4),--(OUTBOUND!B:B)) > > This results in a "#NUM in the cell > What have I missed? > > > "JLatham" wrote: > > > Norm, first - I think I provided a fix to the problem in the other discussion > > on VLOOKUP(). Check it out. > > > > Now, for this problem. If Row 16 on Sheet2 is the one where you have > > Apr11-17 shown in your example, that's probably not a real date and while you > > could use VLOOKUP() to return those values just as before, another way to get > > the values from a column on Sheet2 associated with dates would be like this. > > > > Assumptions: > > Sheet2 has dates in column A > > In sheet 1 you have the start date in C4 and the end of period date in D4 > > (could be any other cell, but D4 for this example). Then > > =SUMPRODUCT(--(Sheet2!$A:$A>=$C$4),--(Sheet2!$A:$A<=$D$4),--(Sheet2!B:B)) > > would give you the total of values in column B as a result. The way I've > > set up the formula, you can then drag it to the right on your sheet and the > > final column will change from B, to C, to D, to E, etc depending on how far > > across the sheet you drag or fill it. > > If you put 11-Apr in C4 and 17-Apr in D4, then it would give you the totals > > for that period. > > > > > > "Norm" wrote: > > > > > I need to get the information from sheet 2 row 16 using the date in sheet 1 > > > C4 and the same date in sheet 2 A16 > > > sheet 1 > > > > > > REPORT DATE 4/12/2010 > > > APR 11 - 18 > > > KPI No Genesis KPI's UOM > > > > > > sheet2 > > > > > > 15-Apr > > > 16-Apr > > > 17-Apr > > > Apr11-17 11 19 10 > > > 18-Apr > > > 19-Apr > > > > > > or can I lookup a sum of cells using a range of dates? > > > Thanks > > > |