If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Nesting Levels
I had that same problem. I eventually resolved it using
the sum funtion in combination with the offset funtion. The offset function allows "sum" cells that are offset from a particular starting point. a b c d e f g h i j n 1 3 2 Acct Jan Feb Mar Apr May Jun Jul Aug Sep YTD Annual 3 Assuming your spread sheet is situated like most accounting period spreadsheets: First, you must have a cell somewhere that identifies the current period. ie, in this case, a1 reading 3 would signify the 3rd period. The formula in the YTD cell for row 3 would be as follows: sum(offset(B3,0,0,1,$A1)) You can read the detail on each reference on the excel help menu for the offest worksheet function, however, the final address of $A1 looks to the "period no" you set up to indicate how many cells to include in the sum. -----Original Message----- Need help with a fomula. Setting up a spreadsheet that will sum the contents of cells if certain conditions are met. The conditions are the months n the year, Jan through Dec. if condition is Jan then return jan sales dollars, if condition is feb, then sum jan and feb sales dollars, if condition is Mar, then sum Jan, Feb and Mar sales dollars, and so on. This way, as the year goes on, the YTD cell will return the YTD sum through the month of the condition. My problem, I am limited to 7 nesting levels. Is there a better way to do this? . |
#2
|
|||
|
|||
Nesting Levels
With your dates in A1:L1 and your values in A2:L2
=SUMIF(A1:L1,"="&TODAY(),A2:L2) Vince Vaughn wrote in message ... Need help with a fomula. Setting up a spreadsheet that will sum the contents of cells if certain conditions are met. The conditions are the months n the year, Jan through Dec. if condition is Jan then return jan sales dollars, if condition is feb, then sum jan and feb sales dollars, if condition is Mar, then sum Jan, Feb and Mar sales dollars, and so on. This way, as the year goes on, the YTD cell will return the YTD sum through the month of the condition. My problem, I am limited to 7 nesting levels. Is there a better way to do this? |
Thread Tools | |
Display Modes | |
|
|