Count Across Sheets

[anths88]

Hi All

I was hoping to find a way to count a specific criteria across 72 sheets without using =SUMPRODUCT(COUNTIF(INDIRECT("'"&tabs!$A$1:$A$72&"'!C:C"),B2)),"") etc

These are the facts in my case
-I have circa 40k loans in one sheet, each which has a unique reference code.
-For each loan, i need to count how many times the loan has been in arrears for more than 30 days.
-To do the above, I have 72 months (tabs) of arrears data (each tab has a column for the unique reference and the number of days it is in arrears at the end of each month)
-Each of the 72 tabs is in this format (mm-yy)
-To further complicate this, it will need to be run monthly and go back only 72 months, though i have tried to minimise this risk in the indirect formula by making the tab lookup dynamic based on the current date.

I was planning on using a formula similar to the above, with the 30 day component also built in (currently it just counts how many times a unique reference is contained within the 72 sheets). Though my biggest concern is that the calculation takes far too long, even on a super computer the calculation never completed.

Is there a formula or macro that i can use to speed up this process?

Thank you in advance
Anthony


ARREARS DATA FORMAT
[TABLE="width: 756"]
<tbody>[TR]
[TD]Product Type
[/TD]
[TD]Reporting Name
[/TD]
[TD]Account Nbr
[/TD]
[TD]Loan Number
[/TD]
[TD] Current Balance
[/TD]
[TD] Arrears Balance
[/TD]
[TD]Days in Arrears
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 756"]
<tbody>[TR]
[TD]
[/TD]
[/TR]
</tbody><colgroup><col span="7"></colgroup>[/TABLE]

 

[anths88]

Would it be quicker to combine the 72 sheets (tabs) and use the countif on this data?

 

[PaddyD]

Hi - welcome to the board.

I'm assuming your 72 sheets are monthly for 7 years..?

Anyway:

"Would it be quicker to combine the 72 sheets (tabs) and use the countif on this data?"

In principle, yes. Add an extra column for the month and off you go with all the inbuilt functions usable as designed (inc pivot tables etc)

With 40,000 * 72 = 2,880,000 records things might slow up a bit in any case but won't fin our until you try.

You could prob even get someone to write you a macro that would do the combining into a single sheet, although not me - vba's not really my thing