7+ Nest If's

C

Channing

I need to nest over 7 IF statements and didn't find the answer I needed in
the other posts. I need to check the month in cell a1 for the month to
determine the YTD Budget. IE if a1= "May", sum(Jan:May),if a1="June",
sum(Jan:June).

Thanks for the help.
 
J

Jimbola

INsteda of using IF statements you may want to consider a sumif function. Eg.

a b c d e f
1 Month > June
2 January February March April May June
3 100 200 300 400 500 600

=SUMIF(A2:F2,"<="&B1,A3:F3)

The formula totals all the figures if the months in row 2 equals or is less
than June in B1. But you need to use full month names. If your set-up is
different or you are using names, re-post with more details.

Hope that helps.

Jimbola
 
T

Tim C

With your budget in C1:C12 and the current month in A1,

If the current month is a number:

=SUM(OFFSET(C1,0,0,A1))

If the current month is a date:

=SUM(OFFSET(C1,0,0,MONTH(A1)))

If the current month is the name of a month:

=SUM(OFFSET(C1,0,0,MONTH(A1&" 1")))

If your budget is in a row instead of a column, take out one of the "0," for
example:

=SUM(OFFSET(C1,0,A1))

Tim C
 
T

Tim C

That last part is backwards. If your budget is in a row instead of a
column, ADD an additional comma, for example:

=SUM(OFFSET(C1,0,0,,A1))

Tim C
 
M

Myrna Larson

Why does it not work? Your original post implies that you have named the
ranges for each month. Is that not correct?
 
K

KL

Hi Myrna,

I assumed the OP did Insert>Name>Create>Top Row. Curiously, if I do
=SUM(Jan:Mar) it works, but my suggested =SUM(INDIRECT("Jan:" & A1)) seems
to only return Jan not the whole range. Must be something about INDIRECT (I
seem to recall having read something about it, but not sure).

Regards,
KL
 
M

Myrna Larson

You're right, it doesn't work. I don't know why -- I think it should. But this
variation does:

=SUM(Jan:INDIRECT(A1))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding variable number of columns 8
Page numbering with Merged Document and IF statements 1
if function (if more that 7 nesting) 2
Drop Down List Dependant Formula 2
nesting more than 7 functions? 11
Nested If Statements 7
what function or formula can I use 6
determine a range using a formula 2

Top