Simple COUNTIF not working

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

I'm banging my head against a wall on this. Can anyone
see why this formula isn't working:

=COUNTIF(Res1:Res8!D8,1)

Where Res1 thru Res8 are eight valid worksheets in the
workbook. This formula should count the occurances of
the number 1 in cell D8 in all eight of the included
worksheets, but instead I am getting the dreaded "#VALUE"
error. A similar formula, =AVERAGE(Res1:Res8!D8), is
correctly calculating the average of the data in that
same range. So why is COUNTIF giving me trouble? Thanks.

Andy
 
Because countif or sumif do not work over multiple sheets AKA 3D, there are
ways to do it,
if indeed your sheet names are structured that way
like Res1 and so on

=SUMPRODUCT(COUNTIF(INDIRECT("'Res"&ROW(INDIRECT("1:8"))&"'!"&CELL("address"
,D8)),1))

if you used these sheet names just for this post then it's probably better
using a range where you put EACH individual sheet name

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!"&CELL("address",D8)),1))

note that you don't really need the cell part it can be changed to

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!D8"),1))

it will be static and you can't increment by copying

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
I will try that, or maybe just add eight countif formulas
together (i.e., =COUNTIF(Res1!D8,1)+COUNTIF(Res2!D8,1)+
etc.). Where the h@#l does excel help tell you that
sumif and countif don't work over multiple sheets? And
why don't they?

There are a number of solutions, once you realize that
the right way to do it will not work. Thanks again.

Andy
 
Where the h@#l does excel help tell you that
sumif and countif don't work over multiple sheets?
Click to expand...

HELP lists the functions that work that way:

==========================================
Functions that can be used in a 3-D reference

SUM - adds numbers

AVERAGE - calculates average (arithmetic mean) of numbers

AVERAGEA - calculates average (arithmetic mean) of numbers; includes text and
logicals

COUNT - counts cells that contain numbers

COUNTA - counts cells that are not empty

MAX - finds largest value in a set of values

MAXA - finds largest value in a set of values; includes text and logicals

MIN - finds smallest value in a set of values

MINA - finds smallest value in a set of values; includes text and logicals

PRODUCT - multiplies numbers

STDEV - calculates standard deviation based on a sample

STDEVA - calculates standard deviation based on a sample; includes text and
logicals

STDEVP - calculates standard deviation of an entire population

STDEVPA - calculates standard deviation of an entire population; includes text
and logicals

VAR - estimates variance based on a sample

VARA - estimates variance based on a sample; includes text and logicals

VARP - calculates variance for an entire population

VARPA - calculates variance for an entire population; includes text and
logicals
============================



And why don't they?
Click to expand...

You should address that question to the designers of the program. This is a
user group.


--ron
 
Ron Rosenfeld said:
HELP lists the functions that work that way:
SUM . . .
AVERAGE . . .
AVERAGEA . . .
COUNT . . .
COUNTA . . .
MAX . . .
MAXA . . .
MIN . . .
MINA . . .
PRODUCT . . .
STDEV . . .
STDEVA . . .
STDEVP . . .
STDEVPA . . .
VAR . . .
VARA . . .
VARP . . .
VARPA . . .
Click to expand...
....

And a fine time this is to repeat something that every Excel user should
know: Bill Gates hasn't gotten rich by wasting money keeping his programs'
documentation up to date or accurate. In addition to the functions listed
above, a bit of simple testing will show that there are several other
functions that take 3D references.

AVEDEV
DEVSQ
GEOMEAN
HARMEAN
KURT
MEDIAN
NPV
RANK
SKEW
SUMSQ

But, oddly, MODE doesn't work with 3D references. My cynical mind has leapt
to the conclusion that the individual who originally implemented MODE just
wasn't as competent as the one who implemented RANK.

Note especially NPV, which is the only order-dependent one of all the
functions that accept 3D references.
You should address that question to the designers of the program.
This is a user group.
Click to expand...

Oh right. As if anyone in Microsoft gives a damn for consistency or adding
*calculation* features to Excel rather than shovelling on more generic
Office UI excreta. How many Excel versions has it now been in which
Microsoft hasn't added any new worksheet functions?

As for the specific issue of SUMIF/COUNTIF not supporting 3D references, for
some obscure reason Microsoft chose to require that the 1st arguments to
both and the 3rd argument to SUMIF be Ranges. Note the capital in Ranges.
Both functions seem to restrict operation to the intersection of these
argument Ranges and their parent worksheets' used ranges. This allows users
to specify entire column ranges without adversely affecting recalculation
speed. However, it reduces flexibility and scope for these functions.

Also note that 3D refeences are *NOT* Ranges in the sense of Range objects,
and neither are they collections of Range objects. They are syntactic
constructs that the formula parser almost certainly shunts off to a
specialized iterator that most functions can't access. This was Microsoft's
chicken wire and bubble gum solution to simulating true 3D spreadsheet
functionality. Get used to it.
 
And a fine time this is to repeat something that every Excel user should
know: Bill Gates hasn't gotten rich by wasting money keeping his programs'
documentation up to date or accurate.
Click to expand...

It certainly is not unusual for me to try something first, and check HELP only
if it doesn't work :-)


--ron
 
Back
Top