Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf or SumProduct
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf or SumProduct
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) "Blddrgn700" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf or SumProduct
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif or Sumproduct? | Excel Discussion (Misc queries) | |||
sumif or sumproduct | Excel Discussion (Misc queries) | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumif or sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF | Excel Worksheet Functions |