zero value from using 3D formula, value is missing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chedd - 18 Jun 2007 15:48 GMT

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.

Reply to this Message

bj - 18 Jun 2007 16:29 GMT

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

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

Reply to this Message

Peo Sjoblom - 18 Jun 2007 16:42 GMT

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

Signature

Regards,

Peo Sjoblom

> Hi
>
[quoted text clipped - 4 lines]
> the
> ages of 18-20.

Reply to this Message

Lori - 18 Jun 2007 17:20 GMT

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

> Hi
>
[quoted text clipped - 5 lines]
> --
> Message posted viahttp://www.officekb.com

Reply to this Message

Lori - 18 Jun 2007 17:39 GMT

Or maybe...

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

> Hi
>
[quoted text clipped - 5 lines]
> --
> Message posted viahttp://www.officekb.com

Reply to this Message