Trailing 3 month sales

[Aaron]

Very simple example of Data I'm working with:

Month Store Sales
1/31/2007 west 400
2/28/2007 west 100
3/31/2007 west 300
4/30/2007 west 500
5/31/2007 west 400
6/30/2007 west 100
7/31/2007 west 300
1/31/2007 east 400
2/28/2007 east 100
3/31/2007 east 300
4/30/2007 east 500
5/31/2007 east 400
6/30/2007 east 100
7/31/2007 east 300

In my formula, I will know the store and I will know the Date. Say east and
5/31/2007. In that case I will want to sum the sales for east for may,
april, and march. (thus the trailing three month sales for east as of may)

In reality I have more criteria columns and need to do trailing 12 months or
more, but a solution to the simple example above will show me the way. I
have built mega Sumproduct formulas where I use date functions to count back
months and add all the results, but the formula's are so long they sometimes
don't fit in a cell.

Any ideas? If it helps I can nest an offset into the formula that contains
the values of may april and march from a seperate table of dates, but I
havn't figured out how to use that to my advantage or if that is even the
right approach.

 

[Tyro]

In reality I have more criteria columns and need to do trailing 12 months or
more, but a solution to the simple example above will show me the way. I

Define "or more"

Tyro

 

[Aaron]

Hi Tyro, thanks for the response. In the formula I'm looking for it would not
matter. I'm hoping there is a formula where the syntax is similiar wheather
I go back three months or 12 months. For example, I can load up an offset
array with 3 dates or 12 dates or 50 dates with only slight variations. I'm
just not sure how to get to my goal?

 

[Tyro]

Try this. I named your Month entries as Dates, Store entries as Stores and
Sales entries as Sales. In E1 I put an ending date, for example, any day in
May 2007 which the formula extends to the last day of May. In E2 I put a
number indicating the number of months to include - for example 3, which
means May, April and March. In E2 I put a store - east. Then in E4 I put the
formula
=SUMPRODUCT(--(Dates>=EOMONTH(E1,-E2)+1),--(Dates<=EOMONTH(E1,0)),--(Stores=E3),Sales).
The formula includes sales from March 1, 2007 through May 31, 2007 and
produces the answer 1200. You may go back as many months as you please. This
formula requires that the Analysis Toolpak add-in be present in versions of
Excel prior to Excel 2007. Will this help?

Tyro

 

[Tyro]

Correction: "In E2 I put a store - east" should read "In E3 I put a store -
east". I can read the post a hundred times and not see the error until I
have posted.

Tyro

 

[Tyro]

The comparisons produce TRUE or FALSE as a result. One of the minuses,
coerces Excel to convert TRUE to -1 and FALSE to 0. The second minus changes
the signs so TRUE becomes 1 and False becomes 0. Then the comparisons are
multiplied with the sales. So if all 3 conditions are met then we have 1 * 1
* 1 * sales equals sales. All other combinations produce 0. Then after the
multiplcations are done, the products are summed. You can step through the
formula using the formula evaluator.

Tyro.

 

[Tyro]

Yet another variation
=SUMPRODUCT((Dates>=EOMONTH(E1,-E2)+1)*(Dates<=EOMONTH(E1,0))*(Stores=E3)*Sales).
Here there is no need for the "--" as the "*" coerces the conversion of
TRUE, FALSE to 1 and 0.

Tyro

 

[Tyro]

Take a look at this site:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

BTW, SUMPRODUCT can do more than multiply. It can also do other things like
division, addition, subtraction and exponentiation. For example A1 = 1, A2 =
2, A3 = 3, B1 = 2, B2 = 2, B3 =2.

=SUMPRODUCT(A1:A3/B1:B3) = 3
=SUMPRODUCT(A1:A3+B1:B3) = 12
=SUMPRODUCT(A1:A3-B1:B3) = 0
=SUMPRODUCT(A1:A3^B1:B3) = 14

Tyro