Summation Problem

Simba1

Board Regular
Joined
Dec 13, 2004
Messages
165
Hello Guys,

I work at a company who's fiscal year runs from December to November. I've been buildilng a spreadsheet for over a month now, and am stuck at one particular spot.

To simplify everything, I have a table that is populated by a User Form. The table has the dates on the left most column as follows:

Mar-05
Apr-05
May-05
Jun-05
Jul-05
Aug-05
Sep-05
Nov-05
Dec-05
Jan-06
Feb-06

Now these dates are dependant on what the user might enter into the form. The user entered Mar-05 which is why the example above begins in March-05. If the user entered May-05, it would have started with May-05 and ended with Apr-06.

I have a column next to the date column that has the sales info for the month. This value is looked up. Below the chart, I simply use a simple sum function to get the total sales. However, I've have been killing myself for the past hour trying to figure out how to calculate the Fiscal 05 sales. In the example above, that would be the sales from Mar-05 to Nov-05. I can enter this in manually, but was wondering if there was a formula I could use to avoid this.

Any help?
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

MrActuary

Board Regular
Joined
Jun 18, 2004
Messages
73
Code:
=SUMPRODUCT(--(A1:A18>=DATE(2005,MONTH(A1),1)),--(A1:A18<=DATE(2005,11,1)),B1:B18)

Column A has dates, column B has Fiscal sales.
 

Simba1

Board Regular
Joined
Dec 13, 2004
Messages
165
MrActuary,

Thank you. (y)

I substituted what I have in my cells with the formulas you have and pasted the following code:

Code:
=SUMPRODUCT(--(B4:B15>=DATE(2005,MONTH(H4),1)),--(B4:B15<=DATE(2005,11,1)),B4:B15)

If my dates are in Column B and Sales in column H, why would this not work?
 

Simba1

Board Regular
Joined
Dec 13, 2004
Messages
165
These are the sales figures I have to the corresponding dates:

Mar-05 $223,687
Apr-05 $106,016
May-05 $124,930
Jun-05 $148,354
Jul-05 $104,447
Aug-05 $101,526
Sep-05 $98,760
Oct-05 $57,768
Nov-05 $94,438
Dec-05 $73,909
Jan-06 $70,103
Feb-06 $138,731

This is the total I get with that formula: $231,482
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

You are summing the dates are you not?

Try:

=SUMPRODUCT(--(A4:A15>=DATE(2005,MONTH(H4),1)),--(A4:A15<=DATE(2005,11,1)),B4:B15)
 

Simba1

Board Regular
Joined
Dec 13, 2004
Messages
165
Thanks for the reply Fairwinds

That formula gave me a zero. My dates are actually in column B (column A is blank). Is this possible? Or am I being too ambitious?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Does "fiscal year runs from December to November" mean, Dec 2004 thru Nov 2005, for example?
 

Simba1

Board Regular
Joined
Dec 13, 2004
Messages
165
Thats right Aladin :p

Fiscal 2005 would be Dec 2004 to Nov 2005
Fiscal 2006 would be Dec 2005 to Nov 2006

and so on...

You have any ideas?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
OK so change A4:A15 back to B but the last ref in the formula must be to where you have the data to sum, not to the dates.
 

Simba1

Board Regular
Joined
Dec 13, 2004
Messages
165
Okay it worked now. Thanks for proof reading my careless mistake Fairwinds! :oops: I've been in front of a computer too long today...gotta get home.


THANKS AGAIN EVERYONE! :pray:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,322
Messages
5,600,951
Members
414,417
Latest member
Nobu

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

Which adblocker are you using?

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
Top