Nesting Levels

[Guest]

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?

 

[Ken Wright]

Numerous, involving either SUMIF, SUMPRODUCT etc. Give us an exmple of your
data and how it is arranged ie what ranges etc, and we'll help you where we can.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Best wishes to all, and hope for a good New year
----------------------------------------------------------------------------

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?

 

[Bob Phillips]

Vince,

Here's one suggestion based upon the numbers being in A2:L2, and testing on
today's month

=SUM(INDIRECT("A2:"&CHAR(MONTH(TODAY())+64)&"2"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

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?

 

[Retail Mike]

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.

 

[2rrs]

With your dates in A1:L1 and your values in A2:L2

=SUMIF(A1:L1,"<="&TODAY(),A2:L2)