Counting data over multiple worksheets

X

xlsuser42

Hi all,

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

Lori

xlsuser42 said:
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 N12>0.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.
Click to expand...

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
N12>0.6.

=SUMPRODUCT((T(INDIRECT(SheetNames&"!d4"))="Division")*(N(INDIRECT(SheetNames&"!n12"))>0.6))

Or..

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