looking for a formula
 

I'm looking for a short formula to add some sales figures.
In excel, I have my set up as the following as a simple example: Starting
from left to right

Cell A1, Cell B1, Cell C1, Cell D1... etc.

Column Headers:
Cell A1 = Item Number
Cell B1 = Jan 2004 Sales
Cell C1 = Feb 2004 Sales
Cell D1 = Mar 2004 Sales ... etc (ending at Cell M1 = Dec 2004 Sales)

In the corresponding rows underneath: (the sales numbers information)
Cell A2 = Item number
Cell B2 = 10
Cell C2 = 15
Cell D2 = 20 ... etc (ending at Cell M2 = 25)

What I want this formula to do would have it calculate the sales based on
the current month.

For example..
If the current month was January 2005, the formula would add from Cell B2 to
M2,
If the current month was February 2005, the formula would add from Cell C2
to M2,
If the current month was March 2005, the formula would add from Cell D2 to M2,
If the current month was April 2005, the formula would add from Cell E2 to
M2, and so on.

Does anywone have any ideas? Thanks

You seem to ask for a horizontal sum (Jan 2005: Sum B2 to M2) instead of
a vertical sum from B2 downwards. Care to elaborate?

Amanda wrote:
I'm looking for a short formula to add some sales figures.
In excel, I have my set up as the following as a simple example: Starting
from left to right

Cell A1, Cell B1, Cell C1, Cell D1... etc.

Column Headers:
Cell A1 = Item Number
Cell B1 = Jan 2004 Sales
Cell C1 = Feb 2004 Sales
Cell D1 = Mar 2004 Sales ... etc (ending at Cell M1 = Dec 2004 Sales)

In the corresponding rows underneath: (the sales numbers information)
Cell A2 = Item number
Cell B2 = 10
Cell C2 = 15
Cell D2 = 20 ... etc (ending at Cell M2 = 25)

What I want this formula to do would have it calculate the sales based on
the current month.

For example..
If the current month was January 2005, the formula would add from Cell B2 to
M2,
If the current month was February 2005, the formula would add from Cell C2
to M2,
If the current month was March 2005, the formula would add from Cell D2 to M2,
If the current month was April 2005, the formula would add from Cell E2 to
M2, and so on.

Does anywone have any ideas? Thanks

Thanks so much for your quick response! :)
Yes, Alan you are correct. That is what I am looking for. In the "Item
Column" (A), I am going to have one item number per row and all their sales
history as described. So the formula would be at the end in Cell N2 for each
item.

"Aladin Akyurek" wrote:

You seem to ask for a horizontal sum (Jan 2005: Sum B2 to M2) instead of
a vertical sum from B2 downwards. Care to elaborate?

Amanda wrote:
I'm looking for a short formula to add some sales figures.
In excel, I have my set up as the following as a simple example: Starting
from left to right

Cell A1, Cell B1, Cell C1, Cell D1... etc.

Column Headers:
Cell A1 = Item Number
Cell B1 = Jan 2004 Sales
Cell C1 = Feb 2004 Sales
Cell D1 = Mar 2004 Sales ... etc (ending at Cell M1 = Dec 2004 Sales)

In the corresponding rows underneath: (the sales numbers information)
Cell A2 = Item number
Cell B2 = 10
Cell C2 = 15
Cell D2 = 20 ... etc (ending at Cell M2 = 25)

What I want this formula to do would have it calculate the sales based on
the current month.

For example..
If the current month was January 2005, the formula would add from Cell B2 to
M2,
If the current month was February 2005, the formula would add from Cell C2
to M2,
If the current month was March 2005, the formula would add from Cell D2 to M2,
If the current month was April 2005, the formula would add from Cell E2 to
M2, and so on.

Does anywone have any ideas? Thanks

So you're looking for:

=SUM(INDEX($B2:$M$2,MONTH(TODAY())):$M2)

Is it not?

Amanda wrote:
Thanks so much for your quick response! :)
Yes, Alan you are correct. That is what I am looking for. In the "Item
Column" (A), I am going to have one item number per row and all their sales
history as described. So the formula would be at the end in Cell N2 for each
item.

"Aladin Akyurek" wrote:


You seem to ask for a horizontal sum (Jan 2005: Sum B2 to M2) instead of
a vertical sum from B2 downwards. Care to elaborate?

Amanda wrote:

I'm looking for a short formula to add some sales figures.
In excel, I have my set up as the following as a simple example: Starting
from left to right

Cell A1, Cell B1, Cell C1, Cell D1... etc.

Column Headers:
Cell A1 = Item Number
Cell B1 = Jan 2004 Sales
Cell C1 = Feb 2004 Sales
Cell D1 = Mar 2004 Sales ... etc (ending at Cell M1 = Dec 2004 Sales)

In the corresponding rows underneath: (the sales numbers information)
Cell A2 = Item number
Cell B2 = 10
Cell C2 = 15
Cell D2 = 20 ... etc (ending at Cell M2 = 25)

What I want this formula to do would have it calculate the sales based on
the current month.

For example..
If the current month was January 2005, the formula would add from Cell B2 to
M2,
If the current month was February 2005, the formula would add from Cell C2
to M2,
If the current month was March 2005, the formula would add from Cell D2 to M2,
If the current month was April 2005, the formula would add from Cell E2 to
M2, and so on.

Does anywone have any ideas? Thanks

Alan... You are my hero!!!! That is exactly what I was looking for! Thank
you sooo much! ;)

"Aladin Akyurek" wrote:

So you're looking for:

=SUM(INDEX($B2:$M$2,MONTH(TODAY())):$M2)

Is it not?

Amanda wrote:
Thanks so much for your quick response! :)
Yes, Alan you are correct. That is what I am looking for. In the "Item
Column" (A), I am going to have one item number per row and all their sales
history as described. So the formula would be at the end in Cell N2 for each
item.

"Aladin Akyurek" wrote:


You seem to ask for a horizontal sum (Jan 2005: Sum B2 to M2) instead of
a vertical sum from B2 downwards. Care to elaborate?

Amanda wrote:

I'm looking for a short formula to add some sales figures.
In excel, I have my set up as the following as a simple example: Starting
from left to right

Cell A1, Cell B1, Cell C1, Cell D1... etc.

Column Headers:
Cell A1 = Item Number
Cell B1 = Jan 2004 Sales
Cell C1 = Feb 2004 Sales
Cell D1 = Mar 2004 Sales ... etc (ending at Cell M1 = Dec 2004 Sales)

In the corresponding rows underneath: (the sales numbers information)
Cell A2 = Item number
Cell B2 = 10
Cell C2 = 15
Cell D2 = 20 ... etc (ending at Cell M2 = 25)

What I want this formula to do would have it calculate the sales based on
the current month.

For example..
If the current month was January 2005, the formula would add from Cell B2 to
M2,
If the current month was February 2005, the formula would add from Cell C2
to M2,
If the current month was March 2005, the formula would add from Cell D2 to M2,
If the current month was April 2005, the formula would add from Cell E2 to
M2, and so on.

Does anywone have any ideas? Thanks

Put in say, N2:

=SUM(OFFSET($M$1,ROWS($A$1:A1),,,-(13-MONTH(TODAY()))))

The formula can be copied down col N
to return similarly for rows 3, 4, etc
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Amanda" wrote in message
...
I'm looking for a short formula to add some sales figures.
In excel, I have my set up as the following as a simple example: Starting
from left to right

Cell A1, Cell B1, Cell C1, Cell D1... etc.

Column Headers:
Cell A1 = Item Number
Cell B1 = Jan 2004 Sales
Cell C1 = Feb 2004 Sales
Cell D1 = Mar 2004 Sales ... etc (ending at Cell M1 = Dec 2004 Sales)

In the corresponding rows underneath: (the sales numbers information)
Cell A2 = Item number
Cell B2 = 10
Cell C2 = 15
Cell D2 = 20 ... etc (ending at Cell M2 = 25)

What I want this formula to do would have it calculate the sales based on
the current month.

For example..
If the current month was January 2005, the formula would add from Cell B2
to
M2,
If the current month was February 2005, the formula would add from Cell C2
to M2,
If the current month was March 2005, the formula would add from Cell D2 to
M2,
If the current month was April 2005, the formula would add from Cell E2 to
M2, and so on.

Does anywone have any ideas? Thanks