Sumproduct

[heaobeer]

Ik heb een leningportefeuille

In kolom A staat clientnummer;
Kolom B staat het leningsbedrag;
Een client kan meerdere leningen hebben.

Ik ben op zoek naar een formule die aangeeft hoeveel clienten binnen een bepaalde range vallen.
B.v.
Client 200 heeft een lening van 700 en 1000.
Client 301 heeft een lening van 1000 en 200
Client 850 heeft een lening van 1600.
Dus
Aantal borrowers tussen 1000 en 1500 is 1
Aantal borrowers tussen 1501 en 2000 is 2

Ik heb de fomule van Mieke =SUM(IF(FREQUENCY(A:A;A:A)>0;1)) om het aantal clienten te bepalen

Ik heb van Jeroen de formule =Sumproduct() die ik kan gebruiken om totalen binnen een bepaalde range te vinden.

Hoe combineer ik deze twee of heeft iemand een andere oplossing?

 

[jzeedijk]

Hoi heaobeer,

Hoe moeilijk wil je het maken?

De oplossing, met dank aan Mieke!
=Count(if(frequency($A1:$A20;(($A1:$A20)*($B1:$B20>=H$13)*($B1:$B20<=H$14)))>0;($A1:$A20);""))

Jeroen

 

[heaobeer]

De onderstaande formule werkt nog niet optimaal.

Zoals je in bijgevoegde spreadsheet ziet wordt er een totaal gegeven van 59, terwijl het totaal aantal borrowers wordt berekend in cel A99 en is 39. Ergens zit een dubbeltelling.

Verder worden in kolom D nu de totalen per lening binnen de range gegeven, dit zou echter per borrower binnen de range moeten worden. Wellicht kan men mij hier ook bij helpen.

 

[jzeedijk]

Hoi heaobeer,

Nu begrijp ik de vraag beter.

Die dubbeltelling is duidelijk, borrower 64065 heeft 2 leningen, 1 valt in de categorie 300,000 < Loan Size <= 325,000, de ander in de categorie 125,000 < Loan Size <= 150,000. Deze borrower wordt dus 2* gerekend.

Het som van de leningen van de borrower bepaald in welke categorie deze wordt ingedeeld, niet een individuele lening.

Houd je vast, hier is ie:
=SOM((SOM.ALS($A$25:$A$97;ALS(INTERVAL($A$25:$A$97;$A$25:$A$97)>0;$A$25:$A$97;"");$B$25:$B$97)>$F6)*(SOM.ALS($A$25:$A$97;ALS(INTERVAL($A$25:$A$97;$A$25:$A$97)>0;$A$25:$A$97;"");$B$25:$B$97)<=$G6))
Invoeren als matrixformule!

Jeroen

 

[heaobeer]

Jeroen,

Je bent een topper.

Kan iemand mij nog helpen een formule voor het bepalen van de bedragen in kolom D van het spreadsheet. Nu geeft het de bedragen weer binnen de range per lening, dit moet worden per borrower.

 

[jzeedijk]

Hoi heaobeer,

Nog een mooie formule:
D6: =SOM((SOM.ALS($A$25:$A$97;ALS(INTERVAL($A$25:$A$97;$A$25:$A$97)>0;$A$25:$A$97;"");$B$25:$B$97)>$F6)*(SOM.ALS($A$25:$A$97;ALS(INTERVAL($A$25:$A$97;$A$25:$A$97)>0;$A$25:$A$97;"");$B$25:$B$97)<=$G6)*SOM.ALS($A$25:$A$97;ALS(INTERVAL($A$25:$A$97;$A$25:$A$97)>0;$A$25:$A$97;"");$B$25:$B$97))

Zonder de hulp van Mieke was ik overigens nooit op deze ideeen gekomen.

Jeroen

 

[heaobeer]

Thanks Jeroen

 

[heaobeer]

Matrix formules en geheugen problemen

De matrix-formules, toegepast op grote hoeveelheden gegevens (15.000 regels), geven geheugen problemen.
Heeft iemand een oplossing om dit te ondervangen?

 

[jzeedijk]

Hoi heaobeer,

Dan komen we eigenlijk een beetje in de richting van een eerdere oplossing, namelijk een extra kolom ergens in een blad:
Zet in bijvoorbeeld M3 van sheet1 deze formule:
=(Countif(A25:A$20000;A25)=1)*(Sumif(A$25:A25;A25;B$25:B25))
en kopieer deze naar beneden.

Staan de borrowers in een ander werkblad pas de bereiken dan aan. De formule wordt dan bijvoorbeeld:
=(Countif([Map1]Blad1!A25:A$999;[Map1]Blad1!A25)=1)*(Sumif([Map1]Blad1!A$25:A25;[Map1]Blad1!A25;[Map1]Blad1!B$25:B25))

Eventueel kun je ipv 1 kolom de kolommen Borrowers en de Loansizes met directe verwijzingen eerst in je spreadsheet opnemen.

Jeroen

 

[heaobeer]

Jeroen,

Thanks again.