zero value from using 3D formula, value is missing
 

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.

--
Message posted via http://www.officekb.com

zero value from using 3D formula, value is missing
 

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

"chedd via OfficeKB.com" wrote:

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.

--
Message posted via http://www.officekb.com

zero value from using 3D formula, value is missing
 

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



--
Regards,

Peo Sjoblom

"chedd via OfficeKB.com" <u18187@uwe wrote in message
news:73e31fcc32c5a@uwe...
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.

--
Message posted via http://www.officekb.com

zero value from using 3D formula, value is missing
 

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

On 18 Jun, 15:48, "chedd via OfficeKB.com" <u18187@uwe wrote:
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.

--
Message posted viahttp://www.officekb.com

zero value from using 3D formula, value is missing
 

Or maybe...

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

On 18 Jun, 15:48, "chedd via OfficeKB.com" <u18187@uwe wrote:
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.

--
Message posted viahttp://www.officekb.com