Counting entries across multiple worksheets

[Dave]

I'm no Excel whizz as you may be able to tell from this note. But if
anyone can help I'd appreciate it.

I have 2 columns of data repeated across 5 worksheets (different
departments etc, the same columns but different data). What I'm trying
to do is create a summary worksheet showing the count of entries across
all worksheets where Column1=x and Column2=y.

I've tried COUNTIF but it didn;t seem to work. Any suggestions on how
to do this ? Is Pivottable the right choice ? Or should I use Access ?
Regards
Dave

 

[Frank Kabel]

Hi
one way:
1. Download the free addin Morefunc.xll (http://longre.free.fr/english/)
2. Try the following array formula
=SUM(IF((THREED('sheet1:sheet5'!A1:A20)="x")*(THREED('sheet1:sheet5'!B1:B20)="y"),1,0))

 

[Dave]

Thanks Frank - this works perfectly !