zero value from using 3D formula, value is missing

  • Thread starter chedd via OfficeKB.com
  • Start date
C

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

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
 
P

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

Lori

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top