SumIf or SumProduct

[Blddrgn700]

Hi All:

I am currently working with a cash forecasting model, it needs to be
enhanced to be more dynamic and user friendly.

The problem at hand is when dating the sales forecast for imput into the
model I use a starting date that is based on the first day of the month, for
example:

6/01/08

Then I add 7 and seperate the month into four weeks so that I have:
Column A Column B
R1 Week 1 = Revenue $.$$ Expected Date of Receipt 6/08/08
R2 Week 2 = Revenue $.$$ 6/15/08
R3 Week 3 = Revenue $.$$ 6/22/08
R4 Week 4 = Revenue $.$$ 6/29/08


Etcetera.....

The above goes on for 5 months. It should feed the cash forecast as the
incoming revenue. However, the cash forecast dates are based on the end of a
week so that each week is summarized on the last friday of a week. In the
case of June:

The dates would be:

6/06/08
6/13/08
6/20/08
6/27/08

So the first set of dates to not coincide with the second set of dates. The
second set of dates extend out for 14-weeks.

I tried using the SumIf function but it would not reconize any of the value
because none of the dates matched.

I thought that the SumProduct may by useful but I did not see any reference
to segmenting week, month and year.

I saw many formulas that broke out month and year. Can anyone suggest a
function that I could use to line up the dates?

Thank you in advance for any insight and help!

Kurtis

 

[Bob Phillips]

Couldn't yuou just use

=SUMIF(A:A,"<"&(M1+6-WEEKDAY(M1)),B:B)

for week 1 where M1 holds the first day of the month, and then


=SUMIF(A:A,"<"&(M1+13-WEEKDAY(M1)),B:B)-=SUMIF(A:A,"<"&(M1+6-WEEKDAY(M1)),B:B)

or betterr still put


=M1+6-WEEKDAY(M1)

in a cell such as N1 and use


=SUMIF(A:A,"<"&N1+7,B:B)-SUMIF(A:A,"<"&N1,B:B)

etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[siva.k]

Try adding a new column with the following formula. This would give every
friday date. With this you can match the cash forecast date. Hope this helps.

Sales Forecast New Column (Friday dates)
6/1/2008 =(6-WEEKDAY(A1,1))+A1
6/8/2008 =(6-WEEKDAY(A2,1))+A2
6/15/2008 =(6-WEEKDAY(A3,1))+A3
6/22/2008 =(6-WEEKDAY(A4,1))+A4
6/29/2008 =(6-WEEKDAY(A5,1))+A5