7+ Nest If's
 

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.

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


"Channing" wrote:

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.

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

"Channing" wrote in message
...
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.

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

"Tim C" wrote in message
...
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

"Channing" wrote in message
...
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.

Hi Channing,

How about this:

=SUM(INDIRECT("Jan:" & A1))

Regards,
KL

"Channing" wrote in message
...
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.

Sorry, this won't work.

KL

"KL" wrote in message
...
Hi Channing,

How about this:

=SUM(INDIRECT("Jan:" & A1))

Regards,
KL

"Channing" wrote in message
...
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.

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


On Sat, 22 Jan 2005 10:22:55 +0100, "KL"
wrote:

Sorry, this won't work.

KL

"KL" wrote in message
...
Hi Channing,

How about this:

=SUM(INDIRECT("Jan:" & A1))

Regards,
KL

"Channing" wrote in message
...
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.

Hi Myrna,

I assumed the OP did InsertNameCreateTop 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



"Myrna Larson" wrote in message
...
Why does it not work? Your original post implies that you have named the
ranges for each month. Is that not correct?


On Sat, 22 Jan 2005 10:22:55 +0100, "KL"

wrote:

Sorry, this won't work.

KL

"KL" wrote in message
...
Hi Channing,

How about this:

=SUM(INDIRECT("Jan:" & A1))

Regards,
KL

"Channing" wrote in message
...
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.

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

=SUM(Jan:INDIRECT(A1))


On Sat, 22 Jan 2005 19:45:22 +0100, "KL"
wrote:

Hi Myrna,

I assumed the OP did InsertNameCreateTop 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



"Myrna Larson" wrote in message
.. .
Why does it not work? Your original post implies that you have named the
ranges for each month. Is that not correct?


On Sat, 22 Jan 2005 10:22:55 +0100, "KL"

wrote:

Sorry, this won't work.

KL

"KL" wrote in message
...
Hi Channing,

How about this:

=SUM(INDIRECT("Jan:" & A1))

Regards,
KL

"Channing" wrote in message
...
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.

Gosh! I've tried all variations but this one. Thanks a bunch.

KL

"Myrna Larson" wrote in message
...
You're right, it doesn't work. I don't know why -- I think it should. But
this
variation does:

=SUM(Jan:INDIRECT(A1))


On Sat, 22 Jan 2005 19:45:22 +0100, "KL"

wrote:

Hi Myrna,

I assumed the OP did InsertNameCreateTop 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



"Myrna Larson" wrote in message
. ..
Why does it not work? Your original post implies that you have named the
ranges for each month. Is that not correct?


On Sat, 22 Jan 2005 10:22:55 +0100, "KL"

wrote:

Sorry, this won't work.

KL

"KL" wrote in message
.. .
Hi Channing,

How about this:

=SUM(INDIRECT("Jan:" & A1))

Regards,
KL

"Channing" wrote in message
...
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.