SOM met variabel bereik

[Basedude]

Mensen,

Stel ik heb in een kolom de volgende gegevens staan:

A
1
2
3
B

A
1
2
3
4
B

A
1
2
B

Hoe kan ik nu excel in de cellen met B de som laten uitrekenen van de bovenliggende cellen tot de eerstvolgende A...dus bij de eerste B zou er 6 komen, vervolgens bij de volgende B 10 en dan 3???? Even voor de duidelijkheid: ik wil niet een hele kolom doorlopen om de formule te in de B-cellen te plakken maar hij moet het automatisch voor die kolom opstellen. :thumb:

 

[Henk]

als je gewoon optellen wil, kun je de volgende formule gebruiken.
zet in B de volgende som

=SOM(A1;A2;A3)

a1 t/m a3 zet je dan de cellen neer die jij wil optellen.

groetjes

 

[Basedude]

Dat snap ik..maar de afstanden tussen elke A en B wisselen dagelijks. Hoe kan ik nu Excel automatisch in die kolom elke cel met B op laten zoeken en dan vanuit die cel tot de eerste bovenliggende A de regels daartussen laten optellen???
Wat ik dus zou willen is dat Excel in de kolom op zoek gaat naar een B en dan vanuit daar terug gaat naar de A en de regels ertussen, de inhoud ervan, optelt.... Ik heb zo het idee dat Excel dat niet echt snapt...of ik

 

[Henk]

ik snap het dan ook niet helemaal:8-0:

sorry

 

[Tsarevitsj]

Gaat het om 1 kolom of meerdere?

 

[Basedude]

1...gelukkig

 

[Tsarevitsj]

Hoi Basedude,

Flink gepuzzelt maar ik denk dat je hiermee wel een eind komt.
Ga eerst naar Extra, Macro en Visual Basic Editor.
Klik op invoegen en dan op Module.
Kopieer onderstaande code naar de module.
Je kunt in de Sheet via Werkbalk- Formulieren een knop plaatsen in de sheet en er met de rechtermuisknop op klikken en dan bij Macro toewijzen kies je Opzoeken01.
Voorwaarde is in dit geval is dat je de cursor eerst plaatst op de eerste A in de kolom en dan pas op de knop klikt, omdat je niet aangaf of het altijd om dezelfde kolom ging. Je kunt de tekst van de MsgBox evt. aanpassen of de MsgBox geheel verwijderen.

Veel succes .

Dim var1 As Variant
Dim var2 As Variant
Dim var3 As Variant
Dim myrange As Range
Sub opzoeken01()
If ActiveCell.Value = "A" Then
ActiveCell.Offset(1, 0).Select
var1 = ActiveCell.Address
Do
opzoeken02
Loop Until ActiveCell.Value = "B"
ActiveCell.Offset(-1, 0).Select
var2 = ActiveCell.Address
ActiveCell.Offset(2, 0).Select
Set myrange = Range(var1, var2)
var3 = Application.WorksheetFunction.Sum(myrange)
ActiveCell.Value = var3
ActiveCell.Offset(1, 0).Select
opzoeken01
Else
Range("A1").Select
MsgBox "Alles is opgeteld."
End If
End Sub

Sub opzoeken02()
If ActiveCell.Value <> "B" Then
ActiveCell.Offset(1, 0).Select
End If
End Sub

 

[Basedude]

Harstikke bedankt...het werkt :thumb:

 

[jzeedijk]

Hoi Basedude,

Het kan ook met een matrix-formule:

B21: =ALS(A21<>"B";"";SOM((RIJ(A$11:A20)>MAX((A$11:A20="A")*RIJ(A$11:A20)))*ALS(ISGETAL(A$11:A20);A$11:A20;0)))
Invoeren als matrix-formule Shift & Ctrl & Enter tegelijk indrukken bij invoeren.

In A11 t/m A999 staan diverse (wisselende) bereiken. Naast de B moet het totaal komen van de daarbovenstaande getallen.

Uitleg:
RIJ(A$11:A20)
Het resultaat is de matrix:
{11;12;13;14;15;16;17;18;19;20}

MAX((A$11:A20="A")*RIJ(A$11:A20))
Het resultaat van deze matrix is {11;0;0;0;0;0;0;18;0;0}
als in A11 en A18 een "A" staat. Het maximum vandeze matrix is 18. Dit komt dus overeen met de laatste A uit je bereik.

(RIJ(A$11:A20)>MAX((A$11:A20="A")*RIJ(A$11:A20)))
Het resultaat van deze matrix is:
{0;0;0;0;0;0;0;0;1;1}
Namelijk de rijnummer 19 en 20 zijn groter dan rijnummer 18.

Het tweede gedeelte geeft de waarden uit het bereik weer, waarbij teksten de waarde 0 krijgen.

In A11 en A18 staan de letters A; in A16 (en B21) staat een B.
In A12 t/m A15 staan 1 t/m 4, In A19 t/m A20 staan 1 en 2.
ALS(ISGETAL(A$11:A20);A$11:A20;0)
Het resultaat van deze matrix is:
{0;1;2;3;4;0;0;0;1;2}

Vermenigvuldigen we deze twee matrixen:
{0;0;0;0;0;0;0;0;1;1}*{0;1;2;3;4;0;0;0;1;2}={0;0;0;0;0;0;0;0;1;2}
En hiervan neem je de som.

Jeroen