formula help

N

Norm

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.


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?
 
J

JLatham

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top