Formula Error
 

I have a spreadsheet where I am trying to sum the totals month on month
depending on which month it is (i.e. as it is now August, I only want to sum
up to August and no further) I have begun my formula:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),0)))))))

$AB$2, $Z$2, etc. are the month/year (they are all 2 cells merged starting
from F2)
$AF$1 contains the formula "=TODAY()"
$F$4:$AC$4 is the sum of April to March, $F$4:$AA$4 is April to February etc.

The example above works fine - working back from March to September.

However, when I get to August and type this:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),IF($AF$1$N$2,SUM(F4: O4),0))))))))

I get an error on the final sum, SUM(F4:04).

Am I being really thick? Is there an easier way of doing it?

Many thanks in advance.

Formula Error
 

Excel will only allow 7 embedded if statements.
it looks like this would work well as a vlookup staement.

"Matt_hull1979" wrote:

I have a spreadsheet where I am trying to sum the totals month on month
depending on which month it is (i.e. as it is now August, I only want to sum
up to August and no further) I have begun my formula:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),0)))))))

$AB$2, $Z$2, etc. are the month/year (they are all 2 cells merged starting
from F2)
$AF$1 contains the formula "=TODAY()"
$F$4:$AC$4 is the sum of April to March, $F$4:$AA$4 is April to February etc.

The example above works fine - working back from March to September.

However, when I get to August and type this:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),IF($AF$1$N$2,SUM(F4: O4),0))))))))

I get an error on the final sum, SUM(F4:04).

Am I being really thick? Is there an easier way of doing it?

Many thanks in advance.

Formula Error
 

You have exceeded Excel's limit of 7 IFs. Without you merged cells, you could
use a simple SUMPRODUCT.

If the date in F1:G1 (merged cells) is April, is there data in both F4 & G4?

(You should avoid using merged cells if at all possible!)

"Matt_hull1979" wrote:

I have a spreadsheet where I am trying to sum the totals month on month
depending on which month it is (i.e. as it is now August, I only want to sum
up to August and no further) I have begun my formula:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),0)))))))

$AB$2, $Z$2, etc. are the month/year (they are all 2 cells merged starting
from F2)
$AF$1 contains the formula "=TODAY()"
$F$4:$AC$4 is the sum of April to March, $F$4:$AA$4 is April to February etc.

The example above works fine - working back from March to September.

However, when I get to August and type this:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),IF($AF$1$N$2,SUM(F4: O4),0))))))))

I get an error on the final sum, SUM(F4:04).

Am I being really thick? Is there an easier way of doing it?

Many thanks in advance.

Formula Error
 

try:

=SUM(INDEX(F4:F4,0):INDEX(F4:AC4,0,MATCH($A$F1,$F$ 1:$AB$1,1)+1))

"bj" wrote:

Excel will only allow 7 embedded if statements.
it looks like this would work well as a vlookup staement.

"Matt_hull1979" wrote:

I have a spreadsheet where I am trying to sum the totals month on month
depending on which month it is (i.e. as it is now August, I only want to sum
up to August and no further) I have begun my formula:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),0)))))))

$AB$2, $Z$2, etc. are the month/year (they are all 2 cells merged starting
from F2)
$AF$1 contains the formula "=TODAY()"
$F$4:$AC$4 is the sum of April to March, $F$4:$AA$4 is April to February etc.

The example above works fine - working back from March to September.

However, when I get to August and type this:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),IF($AF$1$N$2,SUM(F4: O4),0))))))))

I get an error on the final sum, SUM(F4:04).

Am I being really thick? Is there an easier way of doing it?

Many thanks in advance.

Formula Error
 

Thanks Toppers,

The reason the cells are merged in that way is that I have budgets on line 4
and actuals on line 5. For ease of reading F4, for example, contains January
Budget, G5 contains January Actuals. G4 contains no data, nor does F5.

I'll probably have a look at reformatting and try a sum product you suggest.

Damn Excel and the "7 Ifs" rule!

"Toppers" wrote:

You have exceeded Excel's limit of 7 IFs. Without you merged cells, you could
use a simple SUMPRODUCT.

If the date in F1:G1 (merged cells) is April, is there data in both F4 & G4?

(You should avoid using merged cells if at all possible!)

"Matt_hull1979" wrote:

I have a spreadsheet where I am trying to sum the totals month on month
depending on which month it is (i.e. as it is now August, I only want to sum
up to August and no further) I have begun my formula:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),0)))))))

$AB$2, $Z$2, etc. are the month/year (they are all 2 cells merged starting
from F2)
$AF$1 contains the formula "=TODAY()"
$F$4:$AC$4 is the sum of April to March, $F$4:$AA$4 is April to February etc.

The example above works fine - working back from March to September.

However, when I get to August and type this:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),IF($AF$1$N$2,SUM(F4: O4),0))))))))

I get an error on the final sum, SUM(F4:04).

Am I being really thick? Is there an easier way of doing it?

Many thanks in advance.

Formula Error
 

bj - thanks

I'm quite familiar with Vlookup, but never to find values which are "less
than" the lookup_value, or to sum the results.

Is that difficult? - or would i need to rerformat as with Toppers post?

"bj" wrote:

Excel will only allow 7 embedded if statements.
it looks like this would work well as a vlookup staement.

"Matt_hull1979" wrote:

I have a spreadsheet where I am trying to sum the totals month on month
depending on which month it is (i.e. as it is now August, I only want to sum
up to August and no further) I have begun my formula:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),0)))))))

$AB$2, $Z$2, etc. are the month/year (they are all 2 cells merged starting
from F2)
$AF$1 contains the formula "=TODAY()"
$F$4:$AC$4 is the sum of April to March, $F$4:$AA$4 is April to February etc.

The example above works fine - working back from March to September.

However, when I get to August and type this:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),IF($AF$1$N$2,SUM(F4: O4),0))))))))

I get an error on the final sum, SUM(F4:04).

Am I being really thick? Is there an easier way of doing it?

Many thanks in advance.

Formula Error
 

Sorry ignore my last question i missed this one.

I'll let you know how this works

"Toppers" wrote:

try:

=SUM(INDEX(F4:F4,0):INDEX(F4:AC4,0,MATCH($A$F1,$F$ 1:$AB$1,1)+1))

"bj" wrote:

Excel will only allow 7 embedded if statements.
it looks like this would work well as a vlookup staement.

"Matt_hull1979" wrote:

I have a spreadsheet where I am trying to sum the totals month on month
depending on which month it is (i.e. as it is now August, I only want to sum
up to August and no further) I have begun my formula:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),0)))))))

$AB$2, $Z$2, etc. are the month/year (they are all 2 cells merged starting
from F2)
$AF$1 contains the formula "=TODAY()"
$F$4:$AC$4 is the sum of April to March, $F$4:$AA$4 is April to February etc.

The example above works fine - working back from March to September.

However, when I get to August and type this:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),IF($AF$1$N$2,SUM(F4: O4),0))))))))

I get an error on the final sum, SUM(F4:04).

Am I being really thick? Is there an easier way of doing it?

Many thanks in advance.

Formula Error
 

Right - sorry for all of the posting confusion.

the "=SUM(INDEX(F4:F4,0):INDEX(F4:AC4,0,MATCH($A$F1,$F $1:$AB$1,1)+1))"
formula worked a treat.

Thanks to you both for your time.

"Matt_hull1979" wrote:

Sorry ignore my last question i missed this one.

I'll let you know how this works

"Toppers" wrote:

try:

=SUM(INDEX(F4:F4,0):INDEX(F4:AC4,0,MATCH($A$F1,$F$ 1:$AB$1,1)+1))

"bj" wrote:

Excel will only allow 7 embedded if statements.
it looks like this would work well as a vlookup staement.

"Matt_hull1979" wrote:

I have a spreadsheet where I am trying to sum the totals month on month
depending on which month it is (i.e. as it is now August, I only want to sum
up to August and no further) I have begun my formula:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),0)))))))

$AB$2, $Z$2, etc. are the month/year (they are all 2 cells merged starting
from F2)
$AF$1 contains the formula "=TODAY()"
$F$4:$AC$4 is the sum of April to March, $F$4:$AA$4 is April to February etc.

The example above works fine - working back from March to September.

However, when I get to August and type this:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),IF($AF$1$N$2,SUM(F4: O4),0))))))))

I get an error on the final sum, SUM(F4:04).

Am I being really thick? Is there an easier way of doing it?

Many thanks in advance.

Formula Error
 

Alternatives for rows 4 and 5

=SUMPRODUCT(($F$1:$AB$1<=$AF$1)*(MOD(COLUMN($F$1:$ AB$1),2)=0)*($F$4:$AB$4))

=SUMPRODUCT(($F$1:$AB$1<=$AF$1)*(MOD(COLUMN($G$1:$ AC$1),2)=1)*($G$5:$AC$5))



"Matt_hull1979" wrote:

Right - sorry for all of the posting confusion.

the "=SUM(INDEX(F4:F4,0):INDEX(F4:AC4,0,MATCH($A$F1,$F $1:$AB$1,1)+1))"
formula worked a treat.

Thanks to you both for your time.

"Matt_hull1979" wrote:

Sorry ignore my last question i missed this one.

I'll let you know how this works

"Toppers" wrote:

try:

=SUM(INDEX(F4:F4,0):INDEX(F4:AC4,0,MATCH($A$F1,$F$ 1:$AB$1,1)+1))

"bj" wrote:

Excel will only allow 7 embedded if statements.
it looks like this would work well as a vlookup staement.

"Matt_hull1979" wrote:

I have a spreadsheet where I am trying to sum the totals month on month
depending on which month it is (i.e. as it is now August, I only want to sum
up to August and no further) I have begun my formula:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),0)))))))

$AB$2, $Z$2, etc. are the month/year (they are all 2 cells merged starting
from F2)
$AF$1 contains the formula "=TODAY()"
$F$4:$AC$4 is the sum of April to March, $F$4:$AA$4 is April to February etc.

The example above works fine - working back from March to September.

However, when I get to August and type this:

=IF($AF$1$AB$2,SUM(F4:AC4),IF($AF$1$Z$2,SUM(F4:A A4),IF($AF$1$X$2,SUM(F4:Y4),IF($AF$1$V$2,SUM(F4: W4),IF($AF$1$T$2,SUM(F4:U4),IF($AF$1$R$2,SUM(F4: S4),IF($AF$1$P$2,SUM(F4:Q4),IF($AF$1$N$2,SUM(F4: O4),0))))))))

I get an error on the final sum, SUM(F4:04).

Am I being really thick? Is there an easier way of doing it?

Many thanks in advance.

Formula Error
 

"Toppers" wrote...
try:

=SUM(INDEX(F4:F4,0):INDEX(F4:AC4,0,MATCH($A$F1,$F $1:$AB$1,1)+1))
....

$A$F1 is a syntax error. Probably a typo for $AF$1.

What do you believe INDEX(F4:F4,0) accomplishes that a nice, simple F4
wouldn't? Simplify this to

=SUM(F4:INDEX(G4:AC4,MATCH($AF$1,$F$1:$AB$1)))