Nested IF
 

Having extracted data from my database I now need to
sumarise it:
{=SUM(IF(Data!$A$2:$A$3272=$B$5,IF(Data!
$L$2:$L$3272=$B$6,IF(Data!$B$2:$B$3272="ZZZ",IF(Da ta!
$C$2:$C$3272="xx-xx",IF(Data!$D$2:$D$3272<$A$1,IF(Data!
$P$2:$P$3272<$A$1,IF($Q$2:$Q$3272=$A$3,IF(Data!
$F$2:$F$3272<$A$1,1,0)))))))))}

Only I can't.

The "Help" suggest using some sort of Macro???

The summaries are for a range of conditions with two of
them requiring the test on $Q having no value ISBLANK was
unreliable thus the test on $A$3 being a known, empty,
cell.

Any help would be most welcome.

PaulC

Hi
Reason for this: You have exceeded the maximum nested function level
which is seven. Use SUMPRODUCT instead. e.g.
=SUMPRODUCT(--(Data!$A$2:$A$3272=$B$5),--(Data!$L$2:$L$3272=$B$6),--(Da
ta!$B$2:$B$3272="ZZZ"),--(Data!
$C$2:$C$3272="xx-xx"),--(Data!$D$2:$D$3272<$A$1),--(Data!$P$2:$P$3272<$
A$1),--($Q$2:$Q$3272=$A$3),--(Data!
$F$2:$F$3272<$A$1))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html



--
Regards
Frank Kabel
Frankfurt, Germany

"PaulC" schrieb im Newsbeitrag
...
Having extracted data from my database I now need to
sumarise it:
{=SUM(IF(Data!$A$2:$A$3272=$B$5,IF(Data!
$L$2:$L$3272=$B$6,IF(Data!$B$2:$B$3272="ZZZ",IF(Da ta!
$C$2:$C$3272="xx-xx",IF(Data!$D$2:$D$3272<$A$1,IF(Data!
$P$2:$P$3272<$A$1,IF($Q$2:$Q$3272=$A$3,IF(Data!
$F$2:$F$3272<$A$1,1,0)))))))))}

Only I can't.

The "Help" suggest using some sort of Macro???

The summaries are for a range of conditions with two of
them requiring the test on $Q having no value ISBLANK was
unreliable thus the test on $A$3 being a known, empty,
cell.

Any help would be most welcome.

PaulC