Sales Forecast Excel Formula - Help Needed!

bcollins124

New Member
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

Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Excel 2010
ABC
1Month20152014
2Jan850925
3Feb950925
4Mar975950
5Apr925975
6May900
7Jun800
8Jul700
9Aug750
10Sep800
11Oct700
12Nov800
13Dec800
1437006250
Sheet7
Cell Formulas
RangeFormula
C14=SUMPRODUCT(ISBLANK(\$B\$2:\$B\$13)*\$C\$2:\$C\$13)

?

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?

Like this?

Excel 2010
ABC
1Month20152014
2Jan850925
3Feb950925
4Mar975950
5Apr925975
6May900
7Jun800
8Jul700
9Aug750
10Sep800
11Oct700
12Nov800
13Dec800
149950
Sheet7
Cell Formulas
RangeFormula
C14=SUMPRODUCT(ISBLANK(\$B\$2:\$B\$13)*\$C\$2:\$C\$13)+SUM(\$B\$2:\$B\$13)

ADVERTISEMENT
Try

Excel 2007
ABC
3Feb950925
4Mar975950
5Apr925975
6May900
7Jun800
8Jul700
9Aug750
10Sep800
11Oct700
12Nov800
13Dec800
1437006250
15
169950
Sheet3
Cell Formulas
RangeFormula
C16=SUM(SUMIF(\$B\$2:\$B\$13,">"&0,\$B\$2:\$B\$13),SUMIF(\$B\$2:\$B\$13,"",\$C\$2:\$C\$13))

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?

ADVERTISEMENT
You won't have to, try it and see

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.

Last edited:
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
ABCDE
1Month20152014
2Jan850925
3Feb950925
4Mar975950
5Apr925975
6May 900FALSE
7Jun800FALSE
8Jul700FALSE
9Aug750FALSE
10Sep800FALSE
11Oct700FALSE
12Nov800FALSE
13Dec800FALSE
149950
Sheet1
Cell Formulas
RangeFormula
B6=""
E6=ISBLANK(B6)
C14=SUMPRODUCT((\$B\$2:\$B\$13="")*\$C\$2:\$C\$13)+SUM(\$B\$2:\$B\$13)

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!

Replies
5
Views
285
Replies
17
Views
743
Replies
5
Views
255
Replies
1
Views
211
Replies
0
Views
181

Threads
1,196,291
Messages
6,014,509
Members
441,825
Latest member
Lade

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back