Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |