Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I nest more than seven functions in MS Excel? | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
How do I nest an OR function within an AND function? | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |