Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 340
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 340
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can I fix a missing formula bar in Excel? torajudo Excel Worksheet Functions 6 June 28th 06 11:39 PM
formula missing something mmock Excel Discussion (Misc queries) 4 March 29th 06 01:30 AM
missing formula bar Ron New Users to Excel 2 September 28th 05 05:09 PM
Cell formula missing Anthony Excel Discussion (Misc queries) 7 July 14th 05 06:06 PM
Formula is missing Shakeel Excel Worksheet Functions 1 July 11th 05 12:20 PM


All times are GMT +1. The time now is 03:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"