formula help PLEASE PT 2

[Norm]

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

 

[Don Guillett]

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.

 

[JLatham]

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]

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]

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.

 

[Don Guillett]

To add to this. Unless there is something in xl2007 I would still suggest
limiting the rows to only those necessary and use a named defined range to
determine the block. I think it's especially important using vlookup. I once
had a client that did need to use lots of rows so I figured out a way to
have a macro re-define the range names.