zero value from using 3D formula, value is missing

[chedd via OfficeKB.com]

Hi

I am using a 3D formula =SUM(COUNTIF(INDIRECT("'"&{"Apr 07","May 07","Jun 07"}
&"'!F27:F48"),({"<18","<=20"})*{-1,1})) to count ages between certain age
groups, but keep getting a 0value to where there are 8 references between the
ages of 18-20.

 

[bj]

the *{-1,1} appears to be in the countif function
I think you want
=SUM(COUNTIF(INDIRECT("'"&{"Apr 07","May 07","Jun 07"}
&"'!F27:F48"),"<=20"})-SUM(COUNTIF(INDIRECT("'"&{"Apr 07","May 07","Jun 07"}
&"'!F27:F48"),"<18")
entered as array control-shift-enter

 

[Peo Sjoblom]

Try

=SUM(COUNTIF(INDIRECT("'"&{"Apr 07","May 07","Jun
07"}&"'!F27:F48"),">=18"))-SUM(COUNTIF(INDIRECT("'"&{"Apr 07","May 07","Jun
07"}&"'!F27:F48"),">20"))

 

[Lori]

=SUM(COUNTIF(INDIRECT("'"&{"Apr 07","May 07","Jun 07"}&"'!F27:F48"),
{"<18";"<=20"})*{-1;1})

 

[Lori]

Or maybe...

=INDEX(FREQUENCY('Apr 07:Jun 07'!F27:F48,{17,20}),2)