Count Functions across multiple worksheets

  • Thread starter Thread starter Pritesh
  • Start date Start date
P

Pritesh

Hi,

I need some help.

I have a spreadsheet that has upto 30 worksheets in them.
The data in each worksheet is the response from a person
on 60 questions. Therefore for example, the cell B4 has
the same meaning to me for every worksheet. Some of the
respondants have chosen not to answer some questions and
thus some cells may be empty. So for every question, I
need to find out how many empty cells (non-respondants)
there were. The function that I think I should be using
if the countblank function, and I think that the correct
syntax is:
=countblank(sheet1:sheet30!B4)
When I use this function, I get a "#VALUE!" message, which
I cannot understand.

I'm looking for a solution to this. Any help will be
appreciated.

Thanks, Pritesh.
 
If you have to count B4 in 30 sheets use

=30-COUNTA(Sheet1:Sheet3!B4)

will return blanks cells assuming the blanks are empty and not formulas that
returns blank

or you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!"&CELL("address",B4)),"="))

where MySheets is a named range with all the individual sheet names
 
Peo,

Thanks for the help. Unfortunately, the cells that have
these values are actually calculated values based of the
raw data. The raw data is test, and the cels are
writtento look for certain values and input other values
(almost like using a vlookup). So the formula that I have
is:
=sumproduct(countif(indirect("'"&sheets&"'!J3)," "))
....and I still get the #VALUE! result.

I'll keep trying...
 
If indeed the empty cells are retrieved by formulas then there is no built
in excel function that can count them
I also noted " " in the formula, don't know what that would mean, looks like
a space to me? Also when you
put the sheet names in a named range you need to put each sheet individually
not like Sheet1:Sheet30!.
but in this case it wouldn't matter since excel is not a very good 3D
spreadsheet. You might want to look at Laurent Longre's morefunc (just do a
websearch) He has a function there called THREED and it could count blanks
like this

=SUMPRODUCT(--(THREED(Sheet1:Sheet30!B4)=""))
 
Peo,

The problem was that the worksheets had spaces in their
names. After I took away the spaces, everything worked
out just fine according to your first suggestion.

Thanks for all your help.

-Pritesh.
 
You can leave the spaces if you want, but you need to enclose the reference in
single quotes. Having got it working, if you now put spaces in your sheet
names, you will see that the formulas adjust automatically to include these
quotes.
 
Back
Top