Sales Forecast Excel Formula - Help Needed!

[bcollins124]

Task: I need to create projected sales forecast for each of the remaining months of the 2015

I am trying to create a formula that sums the current months totals and all those that precede it in Column B. The once catch is that cell B5 through B12 must refer to the prospective sales figures in prior year (Column C) because there is nothing to total yet for in 2015.

So basically I would to create a formula that sums B1 through B4 and C5 through C12. My only question is, in two weeks I am going to have to enter the sales results for May in cell B5 but I don't want to the formula to sum the sales in cell C5 - how can I make it so I don't have to adjust the formula range each month?


--A------B-----C
Month 2015 2014

1 Jan. 850 925
2 Feb. 950 925
3 Mar. 975 950
4 Apr. 925 975
5 May --- 900
6 Jun. --- 800
7 Jul. --- 700
8 Aug. --- 750
9 Sep. --- 800
10 Oct. --- 700
11 Nov. --- 800
12 Dec. --- 800

TOTAL $3,700 $10,025

 

[sheetspread]

Excel 2010
	A	B	C
1	Month	2015	2014
2	Jan	850	925
3	Feb	950	925
4	Mar	975	950
5	Apr	925	975
6	May		900
7	Jun		800
8	Jul		700
9	Aug		750
10	Sep		800
11	Oct		700
12	Nov		800
13	Dec		800
14		3700	6250
Sheet7

Cell Formulas
Range	Formula
C14	=SUMPRODUCT(ISBLANK($B$2:$B$13)*$C$2:$C$13)

?

 

[bcollins124]

I tested the formula above and I got "$0"

Basically I want to create a formula that will total cells B2 through B5 and C6 through C12 - the total should be $9,950.

Any Idea on how to get to $9,950 without having to manually plug in cells B2 through B5 and C6 through C12 each month?

 

[sheetspread]

Like this?

Excel 2010
	A	B	C
1	Month	2015	2014
2	Jan	850	925
3	Feb	950	925
4	Mar	975	950
5	Apr	925	975
6	May		900
7	Jun		800
8	Jul		700
9	Aug		750
10	Sep		800
11	Oct		700
12	Nov		800
13	Dec		800
14			9950
Sheet7

Cell Formulas
Range	Formula
C14	=SUMPRODUCT(ISBLANK($B$2:$B$13)*$C$2:$C$13)+SUM($B$2:$B$13)

 

[Michael M]

Try

Excel 2007
	A	B	C
3	Feb	950	925
4	Mar	975	950
5	Apr	925	975
6	May		900
7	Jun		800
8	Jul		700
9	Aug		750
10	Sep		800
11	Oct		700
12	Nov		800
13	Dec		800
14		3700	6250
15
16			9950
Sheet3

Cell Formulas
Range	Formula
C16	=SUM(SUMIF($B$2:$B$13,">"&0,$B$2:$B$13),SUMIF($B$2:$B$13,"",$C$2:$C$13))

 

[bcollins124]

Basically for the month of April, I would want to sum cells B3 B4 B5 C6 C7 C8 C9 C10 C11 C12 C13

My only question is, next I am going to have to enter the sales results for May in cell B6 but I don't want to the formula to sum the sales in cell C6 - how can I make it so I don't have to adjust the formula range each month? is there a way to automate?

 

[sheetspread]

You won't have to, try it and see

 

[bcollins124]

Thanks again for your help, I tested your formula in a different spreadsheet and it works perfectly. However, the problem I am having with the current worksheet is that the data in columns B and C are "sum totals" that have been transfered from another worksheet in the workbook and for some reason it is not giving me the correct answer. The answer I get is $3,700 (basically the total for column B).

=SUMPRODUCT(ISBLANK($B$2:$B$13)*$C$2:$C$13)+SUM($B$2:$B$13)

Any idea why this is happening? I am wondering if it is because the data has been transferred from another worksheet.

 

[sheetspread]

Those cells may not be blank (because a formula is returning "" or previously did and was pasted as a value) either delete them, use Michael's formula which better handles this, or change the one I gave you for C14 to: =SUMPRODUCT(($B$2:$B$13="")*$C$2:$C$13)+SUM($B$2:$B$13)

Excel 2010
	A	B	C	D	E
1	Month	2015	2014
2	Jan	850	925
3	Feb	950	925
4	Mar	975	950
5	Apr	925	975
6	May		900		FALSE
7	Jun		800		FALSE
8	Jul		700		FALSE
9	Aug		750		FALSE
10	Sep		800		FALSE
11	Oct		700		FALSE
12	Nov		800		FALSE
13	Dec		800		FALSE
14			9950
Sheet1

Cell Formulas
Range	Formula
B6	=""
E6	=ISBLANK(B6)
C14	=SUMPRODUCT(($B$2:$B$13="")*$C$2:$C$13)+SUM($B$2:$B$13)

 

[bcollins124]

After further review I noticed that my spreadsheet had some hidden values in column B (cells B6-B13). I have since deleted the values and the formula is working exactly how I want it to. Thanks again for your help!