| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how can I fix a missing formula bar in Excel? | Excel Worksheet Functions | |||
| formula missing something | Excel Discussion (Misc queries) | |||
| missing formula bar | New Users to Excel | |||
| Cell formula missing | Excel Discussion (Misc queries) | |||
| Formula is missing | Excel Worksheet Functions | |||
Hybrid Mode
