Counting data over multiple worksheets
 

Hi all,

I have run into the need to be able to count data over a large number of worksheets that all have an identical format (so cell D4 has the same type of data in it on every page). I want to be able to count the number of occurances that cell D4="Division" and N120.6 I currently use a combination of sumproduct, countif, and indirect, but I can't figure out how to get it to count based on multiple criteria. Thanks in advance for any help that you might be able to give.

Counting data over multiple worksheets
 

xlsuser42 wrote:
Hi all,

I have run into the need to be able to count data over a large number
of worksheets that all have an identical format (so cell D4 has the
same type of data in it on every page). I want to be able to count the
number of occurances that cell D4="Division" and N120.6 I currently
use a combination of sumproduct, countif, and indirect, but I can't
figure out how to get it to count based on multiple criteria. Thanks
in advance for any help that you might be able to give.




--
xlsuser42

Let SheetNames be the array of worksheet names
[either as a list or by defining the name SheetNames to refer to
=get.workbook(1)].

The formulas below count the number of sheets where D4="division" and
N120.6.

=SUMPRODUCT((T(INDIRECT(SheetNames&"!d4"))="Divisi on")*(N(INDIRECT(SheetNames&"!n12"))0.6))

Or..

=SUMPRODUCT(--(MMULT({1,1},COUNTIF(INDIRECT(SheetNames&{"!d4";"! n12"}),{"Division";"0.6"}))=2))