Count and total the unknown matching data occurences across multi worksheets

Hello again all,

I am working on a yearly report for which each month has its own worksheet.
On each worksheet is a list of data. For arguement sakes the column i am interested in has the value 1-10.

What i want to achieve on a summary worksheet is how many 1, 2, 3 etc occur in my table for each month.

the complicating issue i have is that the numbers are infact random (anything up to 999), they do repeat but not all values up to 999 will be used. So its not as simple as creating a formular for numbers 1-10 (or 999) because i do not know what values to search for.

I would like to create a basic summary table which i would set up with Jan-Dec along the top and then if possible the left hand column a list of the values found ie, 5, 7, 19, 55, 101....and so on which would then total the amount of occurences under the corresponding month.

each month could bring different numbers so the list on the left hand would need to grow subject to what the rest of the year brings.


I'll be honest this is out my league and i'm not sure if what i am asking is achievable. If you can point me in the right direction or provide and advise it will be much appreciated...


thanks Martin

Hi, Give this a try:-
The code assumes you Have 12 sheets in you workbook. ( 1 for each Month)
The Data (Numbers)for each sheet are in column "A".
This code will add a sheet "Summary",(If it does not exist from previous run).
The code will find Unique Numbers and there count from each sheet.
The results are places in column "A" & "B" of sheet "Summary".

Please Login or Register  to view this content.

Regards Mick

Hey guys'

i have uploaded a very basic version of what i see and want.

mike i tried your code, it was my first time but managed to get it loaded. it sort of worked.

but i would like to see the information as shown on the stats page.

just a thought witht the numerical 1-999 could i just set up a basic countif table with 999 rows and use conditional formatting to hide rows with no information?


thanks guys

Hi Try your reworked file Attached:- See sheet "Stat"
Regards Mick

Hey Mickg

that is superb this will definately help.

thanks again martin