Formula Error

[Guest]

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:AA4),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:AA4),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.

 

[Guest]

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

 

[Guest]

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!)

 

[Guest]

try:

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

 

[Guest]

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!

 

[Guest]

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?

 

[Guest]

Sorry ignore my last question i missed this one.

I'll let you know how this works

 

[Guest]

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.

 

[Guest]

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))

 

[Harlan Grove]

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)))