COUNTIF on Multiple sheets

**wjsok85** · 03-08-2009, 06:12 AM

Hi,

I have a workbook with alot of sheets, each sheet is identical in format. What I am trying to achive is a way of counting all the occurances of a name in a range of cells on all sheets.

To try and explain, each sheet has a drop down list in cells C5:V5, and I need to try and get a way of a summary sheet showing how many times "J.Bloggs" appears in All Sheets accorss the range C5:V5, but am finding it impossible.

Any help grately appreciated

Thanks

Wil

**DonkeyOte** · 03-08-2009, 07:34 AM

Wil, running conditional tests across sheets is a non-trivial exercise I'm afraid.

There are a few approaches which are discussed (briefly) here: http://www.mcgimpsey.com/excel/threedsumif.html

(In the above they discuss a SUMIF - you are using COUNTIF approach obviously but the premise remains the same)

**wjsok85** · 03-08-2009, 07:46 AM

Hi,

Thanks for that, Currently getting the end result by using the COUNTIF on each sheet for each 'if' and then using SUM(Start:Spacer!A1) to bring back my total, this works great but unfortuneately there are over 123 sheets each with 336 COUNTIF formula's - thus making the workbook rather large (without user data!) and prone to freezing upon open.

Unfortuneatley I also add, change and delete worksheets regulary, so it would seems the SUMPRODUCT approach is also not the solution and as the workbook is used on the network by users at different workstations I guess the morefunc.xll is a no go either!

I may just have to admit defeat?

Wil

**DonkeyOte** · 03-08-2009, 10:10 AM

morefunc.xll can be embedded into the file itself I believe so it does need to be installed directly on all clients using the file (I believe there are issues using embedded feature in XL2007 or at least I seem to recall having read that somewhere)

re: Sumproduct, you can use VBA obviously to generate a listing of worksheets in the file at any given time and in turn use Dynamic Named Range to increase/reduce in size as required but I would say if you can use morefunc.xll that's not a bad way to go (IMO).

**wjsok85** · 03-08-2009, 10:27 AM

Ah-ha!

Did not realise that Morefunc could be embedded, have downloaded, installed, embedded, calculated and all works ok...

Thanks for you help, thats now the second time you have saved me from going bald.

Regards,

Wil