Coding for a search and count array using VB

Hello All,

I have an excel formula that works great. The problem is that This formula is repeated 5 times in each cell (to represent each worksheet that needs to be summarized and counted). There are 10 cells with this formula and it crashes my system every time I try to save it. The one time I did save it, it took 40 minutes. Here is the formula in short version:

Please Login or Register  to view this content.

I need to find a way to do this via macros in hopes that my spreadsheet will not die every time I add something.

Basically, what I am trying to do is see if in Column G you find EC1
If you find EC1, I need to know if column D is populated and count up how many times this happens over 5 spreadsheets.
The catch is that if D is repeated within the same spreadsheet, I do not want the repeats to be counted.
These are spreadsheets that are constantly growing so I have set the following code, so far but I am not sure I am on the right track. I was thinking about a few if then statements, but am new at the coding and do not know how to program for the duplicates.

Please Login or Register  to view this content.

Is there anyone that can help me with the vb programing for this. I am attaching a spreadsheet that may help show my problem.

Thank you in advance for any and all assistance.
Much Regards,
jsgray

Hello jsgray,

Just to be sure I follow you correctly, the sum for the example in the workbook would be 8, yes?

I suspect were you to add some Dynamic Named Ranges you might notice some improvement in performance

Presently you're referencing >65000 cells in each precedent range - Arrays are iterative by nature and do not (unlike most functions) work with just the used range intersect.

Create a Name

Please Login or Register  to view this content.

with the above in place your unique count formula becomes:

Please Login or Register  to view this content.

So now the above is only referencing the necessary rows - that said if you have tens of thousands of rows of data then it will still be slow.

Hello Justin,

Do you want totals for each sheet in different cells or a single grand total in one cell?

Hello jcgray,

The following UDF has been added to the attached workbook. This will sum all "EC1" values on all the worksheets. Repeats in column "D" are ignored.

Please Login or Register  to view this content.

Hello Justin,

I can better answer your questions if you post your workbook.

Hello Justin,

The macro now ignores blank cells. On "Sheet1" is now a button to run the macro. The total is copied to "C21". You can change this whichever location you want. The attached workbook has all these changes made to it.

Please Login or Register  to view this content.