Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Amanda
 
Posts: n/a
Default 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
  #2   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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

  #3   Report Post  
Amanda
 
Posts: n/a
Default

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


  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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


  #5   Report Post  
Amanda
 
Posts: n/a
Default

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




  #6   Report Post  
Max
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 02:49 PM
Simple formula doesn't quite add up circeo Excel Discussion (Misc queries) 3 January 17th 05 10:04 PM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 02:27 AM
Formula Result Correct but value in the cell is wrong jac Excel Worksheet Functions 2 December 17th 04 09:05 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM


All times are GMT +1. The time now is 10:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"