Counting across sheets

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

I'm trying to count a specific occurance across multiple
sheets. My cell range stays constant over the sheets.
How do I define this sheet range?

For cells M6:M20, I'm looking for the occurance of LC in
sheets 1,2,3,4,5,6

COUNTIF('sheet 1'!M6:M20,"LC") will look at sheet 1
only. How do I rewrite so that it'll look across all the
sheets?
 
Sadly, COUNTIF does not work on 3D ranges. Your best bet is probably to have
a cell in each sheet that calculates the COUNTIF formula for that sheet, and
then sum this cell across all the sheets.
 
One way

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:6"))&"'!M6:M20"),"LC")
)

or with better flexibility since the above is a hard coded solution using
the default sheet names (Sheet1, Sheet2 etc)

=SUMPRODUCT(COUNTIF(INDIRECT("'"&F1:F6&"'!M6:M20"),"LC"))

where F1:F6 holds the list of all the sheet names
 
Awesome! Thanks to all three of you. I really
appreciate the help and will attempt the various
strategies. Good day!
 
You may try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. e.g. you may use the following formula
COUNTIF(THREED('sheet 1:sheet6'!M6:M20),"LC")
Click to expand...
...

Did you test this? Apparently not. THREED returns an *ARRAY*, **NOT** a range.
COUNTIF (and SUMIF) accept **ONLY** ranges are first arguements. So the formula
above with MOREFUNC.XLL properly installed will always return #VALUE!.

Better to use MOREFUNC's COUNTIF.3D function.
 
Back
Top