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
<tbody>
</tbody>
<tbody>
</tbody><colgroup><col span="7"></colgroup>
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
Product Type | Reporting Name | Account Nbr | Loan Number | Current Balance | Arrears Balance | Days in Arrears |
<tbody>
</tbody>
|
<tbody>
</tbody><colgroup><col span="7"></colgroup>