3 dimensional COUNTIF

[Faraz A. Qureshi]

Any idea how to carryout an exercise of counting the number of times an entry
appears in multiple sheets' cells A1?

In other words a formula like:
=COUNTIF(Sheet1:Sheet10!A1,"X")

So as to checkout the number of sheets carrying "X" in their respective cell
A1?

 

[Jacob Skaria]

Hi Faraz

Try this; if your sheets are named Sheet1,Sheet2,Sheet3...Sheet10 etc; then

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet" &ROW(1:10) &"'!A1"),"x"))

If this post helps click Yes

 

[Mike H]

Hi,

The excellent addin Morefunc has just such a 3D formula

http://xcell05.free.fr/morefunc/english/

You would then use

=COUNTIF.3D(Sheet1:Sheet10!A1,"x")

Mike