Countif vs sumif

[heaobeer]

In een spreadsheet bestaat kolom a uit een reeks clientennummers, kolom b uit een reeks vestigingsnummers en kolom c uit een reeks leningsbedragen.

Een client kan meerdere leningen hebben. Tevens kan een clientnummer op meerdere vestigingen voorkomen (dit is dus geen uniek nummer).

Om het totale leningsbedrag per client te bepalen gebruik ik de volgende formule: =(Countif(A1:A100;A1)=1)*(sumif(A1:A100;A1;C1:C100)).

Ik wil nu het totale leningsbedrag bepalen per client kunnen bepalen, maar rekening houdend dat een clientnummer niet uniek is per vestiging.

Wie kan mij helpen?

 

[Jan van Asseldonk]

Volgens mij kun je het gedeelte met countif volledig weglaten. Met alleen sumif heb je voldoende.

 

[heaobeer]

Ok, maar hoe zou ik dan het totale leningsbedrag per client kunnen bepalen als het bedrag aan twee voorwaarden moet voldoen (clientnummer en vestigingsnummer)?

 

[jzeedijk]

Hoi Heaobeer,

Niet SUMIF gebruiken, maar SUMPRODUCT!

Blad2!C2: =Sumproduct((Blad1!A1:A100=Blad2!A1)*(Blad1!B1:B100=Blad2!B1)*(Blad1!C1:C100))

Met:
Blad2!A1 Clientnummer
Blad2!B1 Vestigingsnummer
Blad1!A1:A100 Alle clientnummers
Blad1!B1:B100 Alle Vestigingsnummers van de clienten
Blad1!C1:C100 Alle saldo's op de rekeningen

Jeroen

 

[heaobeer]

Jeroen,

Dank je voor je reactie. Met jouw oplossing is mijn probleem nog niet opgelost.

Een tweede blad met een clientnummer en vestigingsnummer is niet mogelijk. Deze informatie is voor hetgeen ik het nodig heb ook niet relevant. Ik moet de totaal geleende bedragen voor de clienten in een leningportefeuille bepalen.

In een portefeuille zitten een aantal clienten (A). Een client kan meerdere leningen (C) hebben. Als de lening met hetzelfde clientennummer, maar bij een andere vestiging (B) is afgesloten, dan wordt dit beschouwd als een andere client.

Ik heb een formule nodig die in kolom D berekend wat het totale leningsbedrag per client is. rekeninghoudend met (1) clientennummer en (2) vestigingsnummer.

Thanks

 

[jzeedijk]

Hoi Heaobeer,

Wil je de totalen in koolm D?!? Dat betekent dat als een client vaker voorkomt het bedrag meerdere keren in de lijst verschijnt, of wil je dat het bedrag van de client op die vestiging alleen bij de eerste (of laatste) rekening verschijnt?

D1: =Sumproduct(($A$1:$A$100=$A1)*($B$1:$B$100=$B1)*($C$1:$C$100))

Alleen een bedrag bij eerst keer Client op vestiging:
D1:=IF(Sumproduct(($A$1:$A1=$A1)*($B$1:$B1=$B1))=1;Sumproduct(($A$1:$A$100=$A1)*($B$1:$B$100=$B1)*($C$1:$C$100)) ;"")

Alleen bij de laatste keer client op vestiging:
D1:=IF(Sumproduct(($A$100:$A1=$A1)*($B$100:$B1=$B1))=1;Sumproduct(($A$1:$A$100=$A1)*($B$1:$B$100=$B1)*($C$1:$C$100)) ;"")

Jeroen

 

[heaobeer]

Jeroen,

Dank je wel. Je bent een topper.

Kan jij uitleggen wat de logica is van de sumproduct formule?

 

[jzeedijk]

Hoi heaobeer,

De logica van somproduct, eigenlijk exact wat er staat.
De formule telt een aantal producten bij elkaar op.

Met een voorbeeld wordt het duidelijker:
In Range A1:A10 zet je 1 t/m 10
In Range B1:B10 zet je 10 t/m 1

In A12 zet je de formule =(A1:A10)*(B1:B10)
In A13 zet je de formule =somproduct((A1:A10)*(B1:B10))

Het resultaat van A12 (F2 en daarna F9):
={10\18\24\28\30\30\28\24\18\10}

Het resultaat van A13 is 220, namelijk de optelling van de bovenstaande matrix.
Somproduct doet dus:
=1*10+2*9+3*8+....+10*1

Nu kun je in somproduct ook werken met vergelijkingen:
A12: =(A1:A10>5)*(B1:B10)
={0\0\0\0\0\5\4\3\2\1}

Deze formule kijkt of Ax groter is dan 5 zo ja komt er een 1 (WAAR) anders een 0 (ONWAAR). Het resultaat van de somproduct-formule zal 15 zijn.

Als je dit door hebt kun je enorm gaan spelen met de formule:
A12: =(A1:A10>2)*(A12:A10<8)*(B1:B10)
Oftwel tel 8 t/m 4 bij elkaar op.
={0\0\8\7\6\5\4\0\0\0}, resultaat somproduct is 30

Maar dat is nog niet alles. Je mag ook veel functies in Somproduct zetten:
=somproduct(((A1:A10)*(B1:B10)=max((A1:A10)*(B1:B10)))*(A1:A10)*(B1:B10))
Oftewel tel de resultaten op die gelijk zijn aan het maximum van je eerste formule (resultaat 60)

Of als je in kolom A datums zet:
=somproduct((Maand(A1:A10)=1)*(B1:B10))
Geeft als resultaat de optelling van de waarden uit januari.

Er zijn echter ook een paar beperkingen:
- De bereiken moeten even lang zijn:
=somproduct((maand(A1:A10)=1)*(B1:B20))
geeft een foutmelding
- de bereiken met waarden mogen geen tekst bevatten. Al kun je dit omzeilen door de functie te veranderen in
=somproduct((maand(A1:A10)=1)*als(istekst(B1:B10);0;(B1:B10)))
en deze formule als matrix-formule in te voeren (Ctrl+Shift ingedrukt houden als je op Enter drukt)

Kortom er is heel heel veel mogelijk met Somproduct.

Jeroen