Sumproduct Please Help!

[jcastellano]

I have been having a difficult time getting a response to this issue
am having. Hopefully, I can do a better job of explaining the problem.
I have tried every combination I can think of.

I am having my plants forecast weekly receipts and disbursements for a
18 week period time once a month (i.e. every 15th of the month eac
plant will provide a new 18-week forecast). Each week's forecast wil
be in one separate column beginning in column f (i.e. 24-Feb column f
3-Mar column g;10-Mar column h; etc.). Therefore, after the nex
monthly update, column f will be week #1 of the forecast but it will b
a different week ending.

In column c I have several categories of receipts and disbursements fo
which I will receive a forecast. My deliverable to my banks only need
to be 13 weeks, but I have to produce the deliverable weekly
Additionally, I also want to summarize some of the categories in colum
c as I will not need to show all of the detail. Therefore, my firs
deliverable would begin with the week of 24-Feb in column f and g
through 19-May in column r. The following week, my deliverable woul
begin with 3-Mar in column f and go through May 26 in column r. Do yo
see the pattern?

In column e I have created summary categories for the line items I hav
in column c. I have been trying to use the sumproduct category as I wan
to summarize by my categories in column e, yet I want to go to th
source table based on the week ending dates I have in each colum
header. I have discovered that sumproduct will not work with differen
arrays. I posted a thread last night with a pdf sample of what I a
trying to do and I have no response. I don't know if that's due to th
fact that there is a file attached to the thread and users ar
concerned about viruses, or if I am trying to do something crazy here.

I hope someone can help. Very desparate

+-------------------------------------------------------------------
|Filename: SumProduct Table.zip
|Download: http://www.excelforum.com/attachment.php?postid=4456
+-------------------------------------------------------------------

 

[kcc]

Assuming the first number (500) is in cell D4 (you don't show
row numbers), and the first calc (900) is in D20, set D20 to
=SUMPRODUCT(--($B20=$C$4:$C$16)*OFFSET($C$2,2,MATCH(D$19,$D$2:$H$2),ROWS($C$4:$C$16)))
This assumes the dates are in order across the page.

Many people may have ignored it because they didn't want to
deal with a zip of a pdf that has to be converted to excel when
an actual excel file would have been half the size.

kcc

"jcastellano" <[email protected]>
wrote in message