Nesting

[Goliath Cobalt]

I have read several posts regarding nesting, but I haven't quite found
what has perplexed me for the last couple of hours.

I'm using a set of IF functions to check 12 different cells to see if
they match a particular cell. If it does, it returns a value of a
different cell, if false it returns zero. These are contained within a
SUM function.

Now, here is the perplexing part. They aren't "nested" per se, because
they are all on the same level. I can get it to work by checking up to
11 of the cells, but on the 12th, it breaks.

Here is the formula with only the 11 entries:

=SUM(IF($B$8=B$83,$B9,0),IF($E$8=B$83,$E9,0),IF($H$8=B$83,$H9,0),IF($K
$8=B$83,$K9,0),IF($N$8=B$83,$N9,0),IF($Q$8=$B83,$Q9,0),IF($T$8=B
$83,$T9,0),IF($W$8=B$83,$W9,0),IF($Z$8=B$83,$Z9,0),IF($AC$8=B
$83,$AC9,0),IF($AF$8=B$83,$AF9,0))

The only difference with 12 is the very end, which contains this just
before the last )

,IF($AI$8=B$38,$AI9,0)

So that the whole thing would look like:

=SUM(IF($B$8=B$83,$B9,0),IF($E$8=B$83,$E9,0),IF($H$8=B$83,$H9,0),IF($K
$8=B$83,$K9,0),IF($N$8=B$83,$N9,0),IF($Q$8=$B83,$Q9,0),IF($T$8=B
$83,$T9,0),IF($W$8=B$83,$W9,0),IF($Z$8=B$83,$Z9,0),IF($AC$8=B
$83,$AC9,0),IF($AF$8=B$83,$AF9,0),IF($AI$8=B$83,$AI9,0))

Yet the first long formula above works, the second long one does not.

Any thoughts? If it is counting this towards nesting, then something
odd is going on, because I'd be nested 11 times already.

Any help would be greatly appreciated.

 

[Goliath Cobalt]

I have read several posts regarding nesting, but I haven't quite found
what has perplexed me for the last couple of hours.

I'm using a set of IF functions to check 12 different cells to see if
they match a particular cell. If it does, it returns a value of a
different cell, if false it returns zero. These are contained within a
SUM function.

Now, here is the perplexing part. They aren't "nested" per se, because
they are all on the same level. I can get it to work by checking up to
11 of the cells, but on the 12th, it breaks.

Here is the formula with only the 11 entries:

=SUM(IF($B$8=B$83,$B9,0),IF($E$8=B$83,$E9,0),IF($H$8=B$83,$H9,0),IF($K
$8=B$83,$K9,0),IF($N$8=B$83,$N9,0),IF($Q$8=$B83,$Q9,0),IF($T$8=B
$83,$T9,0),IF($W$8=B$83,$W9,0),IF($Z$8=B$83,$Z9,0),IF($AC$8=B
$83,$AC9,0),IF($AF$8=B$83,$AF9,0))

The only difference with 12 is the very end, which contains this just
before the last )

,IF($AI$8=B$38,$AI9,0)

So that the whole thing would look like:

=SUM(IF($B$8=B$83,$B9,0),IF($E$8=B$83,$E9,0),IF($H$8=B$83,$H9,0),IF($K
$8=B$83,$K9,0),IF($N$8=B$83,$N9,0),IF($Q$8=$B83,$Q9,0),IF($T$8=B
$83,$T9,0),IF($W$8=B$83,$W9,0),IF($Z$8=B$83,$Z9,0),IF($AC$8=B
$83,$AC9,0),IF($AF$8=B$83,$AF9,0),IF($AI$8=B$83,$AI9,0))

Yet the first long formula above works, the second long one does not.

Any thoughts? If it is counting this towards nesting, then something
odd is going on, because I'd be nested 11 times already.

Any help would be greatly appreciated.

Nevermind.

 

[Guest]

It's kind of difficult to "nevermind" when your formula:
=SUM(IF($B$8=B$83,$B9,0),IF($E$8=B$83,$E9,0),IF($H$8=B$83,$H9,0),IF($K$8=B$83,$K9,0),IF($N$8=B$83,$N9,0),IF($Q$8=$B83,$Q9,0),IF($T$8=B$83,$T9,0),IF($W$8=B$83,$W9,0),IF($Z$8=B$83,$Z9,0),IF($AC$8=B$83,$AC9,0),IF($AF$8=B$83,$AF9,0),IF($AI$8=B$83,$AI9,0))

could be this, instead:
=SUMPRODUCT(((MOD(COLUMN(B8:AI8),3)=2)*(B8:AI8=B83))*B9:AI9)


Does that help?
***********
Regards,
Ron

XL2002, WinXP