optellen op basis van deels overeenkomende cellen

[jhhogervorst]

Ik heb een Engelstalige Excel met daarin de volgende tabel:

A1 B1 C1 D1 E1
Vluchtnr Ritstart Duur(m) kg
CI0065 1-sep 1-09-11 11:18 0 5
CI0066 1-sep 1-09-11 12:30 18 19
CX270 1-sep 1-09-11 13:15 11 19
CX271 1-sep 1-09-11 8:17 27 13
GA088 1-sep 1-09-11 9:18 16 7
GA089 1-sep 1-09-11 8:18 17 12
CX270 2-sep 2-09-11 13:15 13 18
CX271 2-sep 2-09-11 8:17 26 5
GA088 2-sep 2-09-11 9:18 17 6
GA089 2-sep 2-09-11 8:18 18 13

Ik wil graag de kilogrammen optellen van de vluchten CI0065 en CI0066 van 1 sept. Die 1 sept is belangrijk, want de vluchtnummers komen meerdere dagen voor.
Nu kan ik via =SUMIF(A$2:A$11;"=CI*";E$2:E$11) wel handmatig per vluchtnr de letters intypen, maar ik zoek een functie die het volgende kan:
Op basis van het vluchtnr uit kolom A , van alle vluchten van dezelfde dag met het vluchtnr +1 (of - 1) de kilogrammen uit kolom E optellen.

Met SUMIFS krijg ik het tweede criterium "1-sept" niet herkend, en dus een foutmelding...

Wie kan mij helpen?

 

[Cobbe]

Met behulp van deze heb je meer kans op succes:

=SOMPRODUCT((B3:B20=B3)*(A3:A20=A3)*(E3:E20))

wel nog de bereiken aanpassen.

 

[jhhogervorst]

Beste Cobbe, jouw formule lost het probleem niet op volgens mij, en het lijkt naar mijn bescheiden mening niet de juiste functie te zijn.

Het gaat erom op bijna dezelfde vluchtnummers op dezelfde dag, de kilogrammen op te tellen.

Misschien helpt het als ik een sample bestandje bijvoeg:
Bekijk bijlage KGVluchtensamen.xlsx

 

[Cobbe]

=SOMPRODUCT((B2:B12=B2)*(LINKS(A2:A12;5)=LINKS(A2;5))*(E2:E12))

Het probleem blijft natuurlijk de zoekcreteria. Hoe ga je dat bepalen?

Die LINKS(A2;5) kan je vervangen door een celwaarde.

 

[jhhogervorst]

Ik ben aan het prutsen met sumifs

Het kriterium moet iets zijn met leftA2;2=A:A en B=B (dus 1 sept) dan kolom E optellen

Hoe kan ik Excel laten zoeken naar een celwaarde waarvan de eerste twee letters hetzelfde zijn, maar wel van dezelfde datum?

Andere optie is misschien =VLOOKUP(LEFT(A2;2);A:A;5;TRUE) of iets dergelijks.
Bij mijn weten zoekt ie dan naar alle cellen in kolom A die beginnen met de eerste twee dezefde letters als A2, dus CI. Maar dan moet ie wel alleen die kg's optellen die van 1 september zijn...

 

[Cobbe]

Negeer je mij nu gewoon of wat is het?

=SOMPRODUCT((B2:B12=B2)*(LINKS(A2:A12;5)=LINKS(A2;5))*(E2:E12))

Doet toch precies wat je vraagt maar idg met de linkse 5 tekens.

Om de 2 linkse tekens wijzig je toch gewoon in:

=SOMPRODUCT((B2:B12=B2)*(LINKS(A2:A12;2)=LINKS(A2;2))*(E2:E12))

en die '=LINKS(A2;2) kun je vervangen door een celverwijzing.

 

[jhhogervorst]

Zeker geen kwestie van negeren!

Ik was al begonnen met een antwoord dat het niet werkte, ben toen verder gaan proberen, en heb het antwoord aangepast en deels gewist.

Daarom leek het op negeren.
Ik had slechts eenmaal de 5 in een 2 veranderd bij "left", maar dat moet natuurlijk 2 x:
(LINKS(A2:A12;2)=LINKS(A2;2))

Ik had alleen de tweede dikgedrukte 5 veranderd in een 2.... Dan werkt het niet..


Superbedankt.
Ik zal de status aanpassen naar 'alweer een vraag succesvol opgelost'.

gr
Bob

 

[Cobbe]

Ja oké!

Sorry was een beetje gepiqueerd door iemand anders en heb me zo proberen af te reageren.

Nogmaals excuses en ben blij dat je probleem is opgelost.

 

[jhhogervorst]

Vervolgvraag

Wat nu als ik op tabblad 1 deze gegevens heb:

Vluchtnr Ritstart Duur (m) KG
CI0065 1-sep 1-09-11 11:18 2 5 FORMULE
CX270 1-sep 1-09-11 13:15 11 19
CX270 2-sep 2-09-11 13:15 13 18
GA088 2-sep 2-09-11 9:18 17 6


En op tabblad 2 deze:
Vluchtnr Ritstart Duur (m) KG
CI0066 1-sep 1-09-11 12:30 18 19
CX271 1-sep 1-09-11 8:17 27 13
CX271 2-sep 2-09-11 8:17 26 5
GA089 2-sep 2-09-11 8:18 18 13

Ik zou graag in tabblad 1 op de plek van "FORMULE" de 19 kg van de CI0066 achter de CI 0065 van 1 sept laten verschijnen.
Dus met de left functie moeten de eerste 3 karakters hetzelfde zijn, én het moet van dezelfde datum zijn.
Zo moet dan bijvoorbeeld ook de 5 kg van de CX271 van 2 september, achter de CX270 van 2 september in blad 1 komen.

Kun je me nog 1 keer helpen?

 

[Cobbe]

Ja ik wil je echt nog helpen maar dan moet je die gegevens wel in een bestandje zetten.
Nu moet ik dat allemaal reproduceren en dat is geen plezierig werk.

Heb het dan toch maar gedaan met behulp van een (verborgen) hulpkolom.

=SOMPRODUCT((Blad2!B1:B10=LINKS($A2;3))*(Blad2!C1:C10=$B2)*(Blad2!G1:G10))

 

[jhhogervorst]

Nu met bestand

Bestandje toegevoegd, met twee bladenBekijk bijlage KGHerhalen.xlsx

 

[Cobbe]

Ook in uw bestandje ingevoegd.

 

[jhhogervorst]

Superbedankt.

Hij kan weer op opgelost!