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 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))