Summation Problem

[Simba1]

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?

 

[MrActuary]

=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]

MrActuary,

Thank you.

I substituted what I have in my cells with the formulas you have and pasted the following 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]

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]

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]

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]

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

 

[Simba1]

Thats right Aladin

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]

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]

Okay it worked now. Thanks for proof reading my careless mistake Fairwinds! I've been in front of a computer too long today...gotta get home.


THANKS AGAIN EVERYONE! ray: