[EXCEL] SOM() geeft 0 aan, gewoon optellen werkt wel

[JasperS1965]

Hoi!

Ik moet uit een reeks cellen die volgende tekst bevatten: V4,6A6 de som kunnen geven voor het getal achter de V en de som voor de reeks getallen achter de A.

Om de getallen uit de tekst te halen gebruik ik volgende formules:
=MIDDEN(AB9;VIND.SPEC("A";AB9;1)+1;LENGTE(AB9)-VIND.SPEC("A";AB9;1))
=MIDDEN(AB9;2;VIND.SPEC("A";AB9;1)-2)

Het probleem is echter nu dat als ik SOM gebruik de uitkomst steeds 0 is, terwijl manueel + gebruiken wel werkt (jammer genoeg niet haalbaar omdat het aantal cellen te groot is).

Iemand die mij hiermee kan helpen?

[Lustucru]

Post de exacte formules die je gebruikt bij som en bij het handmatig optellen?

[F_J_K]

Gokje: de cellen zijn niet opgemaakt als getal maar als tekst. Excel herkent het bij + als getal, maar bij som() niet perse. Probeer maar eens:
'1
'2
=A1+A2 = 3
=som(A1:A2) = 0

[DappereDodo]

Celopmaak kan m zijn, maar zet voor de zekerheid WAARDE() om het geheel.

[dix-neuf]

@Jasper, Je kunt je probleem oplossen op deze twee manieren:

1. Handhaaf je beide formules en gebruik voor de optelling (ervan uitgaande dat de uitkomsten van de eerste formule bv. in AC9:AC100 staan):

code:

 =SOM(--(AC9:AC100))

Die somfunctie moet worden ingevoerd via Ctrl-Shift-Enter (dwz: de invoer afsluiten door de Ctrl- en Shifttoets beide tegelijk in te drukken, en terwijl je die ingedrukt houdt op de Entertoets drukken).
OF:
2. Handhaaf je huidige somfunctie en gebruik voor de 2 formules:

code:

 =--(MIDDEN(AB9;VIND.SPEC("A";AB9)+1;LENGTE(AB9)-VIND.SPEC("A";AB9)))
en
=--(MIDDEN(AB9;2;VIND.SPEC("A";AB9)-2))

De uitkomsten worden daardoor getallen.
Deze 2 functies hoeven NIET te worden ingevoerd via Ctrl-Shift-Enter.

Een vraag tot slot: je geeft als voorbeeld 3 cellen waarvan de tekst bij alle drie uit 7 tekens bestaat.
Kan de tekst ook langer zijn dan 7 tekens?
Ofwel: Kunnen de getallen die je zoekt uit meer dan 1 teken, resp. meer dan 3 tekens bestaan (bv: kan er in plaats van V4,6A6 ook V14,6A25 staan)?

[JasperS1965]

Lustucru schreef op woensdag 5 september 2018 @ 12:46:
Post de exacte formules die je gebruikt bij som en bij het handmatig optellen?

DappereDodo schreef op woensdag 5 september 2018 @ 13:18:
Celopmaak kan m zijn, maar zet voor de zekerheid WAARDE() om het geheel.

Heb al geprobeerd om de celopmaak te veranderen naar Getal maar dat had geen baat!

dix-neuf schreef op woensdag 5 september 2018 @ 15:57:
@Jasper, Je kunt je probleem oplossen op deze twee manieren:

1. Handhaaf je beide formules en gebruik voor de optelling (ervan uitgaande dat de uitkomsten van de eerste formule bv. in AC9:AC100 staan):

code:

 =SOM(--(AC9:AC100))

Die somfunctie moet worden ingevoerd via Ctrl-Shift-Enter (dwz: de invoer afsluiten door de Ctrl- en Shifttoets beide tegelijk in te drukken, en terwijl je die ingedrukt houdt op de Entertoets drukken).
OF:
2. Handhaaf je huidige somfunctie en gebruik voor de 2 formules:

code:

 =--(MIDDEN(AB9;VIND.SPEC("A";AB9)+1;LENGTE(AB9)-VIND.SPEC("A";AB9)))
en
=--(MIDDEN(AB9;2;VIND.SPEC("A";AB9)-2))

De uitkomsten worden daardoor getallen.
Deze 2 functies hoeven NIET te worden ingevoerd via Ctrl-Shift-Enter.

Een vraag tot slot: je geeft als voorbeeld 3 cellen waarvan de tekst bij alle drie uit 7 tekens bestaat.
Kan de tekst ook langer zijn dan 7 tekens?
Ofwel: Kunnen de getallen die je zoekt uit meer dan 1 teken, resp. meer dan 3 tekens bestaan (bv: kan er in plaats van V4,6A6 ook V14,6A25 staan)?

Dit is inderdaad de oplossing, bedankt! Had dat nog nooit gezien/gehoord, die -- voor een formule
Het aantal tekens in de cel is inderdaad variabel

[JasperS1965]

Nieuw probleem: in de cellen staat er soms alleen een cijfer ipv V23A3 bijv.
Dit getal zorgt er voor dat mijn formule #WAARDE! als uitkomst geeft.
Hoe kan ik er voor zorgen dat de formule enkel rekening houdt met cellen die beginnen met een V? Of dat die foutmelding weg gaat?

[F_J_K]

Zet er een ALS() omheen: als links(A1;1) een V is dan (huidige functie), anders 0. (Zodat het bij optellen niet meedoet. Let op dat het dan WEL telt voor gemiddelden etc)

[dix-neuf]

JasperS1965 schreef op donderdag 6 september 2018 @ 13:29:
Nieuw probleem: in de cellen staat er soms alleen een cijfer ipv V23A3 bijv.

Na 'bijv' kwam er helaas niets meer, maar stel dat je echt een getal bedoelt (bv. 45 of 8372, dus zonder letter(s)), dan kun je dit gebruiken voor het resultaat van cel AB9:

code:

=ALS(ISGETAL(A9);"";MIDDEN(A9;VIND.SPEC("A";A9)+1;LENGTE(A9)-VIND.SPEC("A";A9)))
en
=ALS(ISGETAL(A9)"";MIDDEN(A9;2;VIND.SPEC("A";A9)-2))

en voor de optelling van AC9:AC100 (invoeren via CTrl-Shift-Enter):

=SOM(ALS(AC9:AC100<>"";--AC9:AC100))

[ Voor 9% gewijzigd door dix-neuf op 06-09-2018 14:04 ]

[xtrme]

=ALS(ISGETAL(VIND.SPEC("V";A9));WAARDE(DEEL(A9;VIND.SPEC("V";A9)+1;VIND.SPEC("A";A9)-VIND.SPEC("V";A9)-1));"")

met som functie

=SOM.ALS(F9:F14;"<>")


zelf denk ik dat het uiteindelijk zoiets moet zijn


=SOMPRODUCT(--((ISGETAL(VIND.SPEC("v";$A$9:$A$15))+ ISGETAL(VIND.SPEC("a";$A$9:$A$15)))>0);DEEL($A$9:A15;VIND.SPEC("V";$A$9:$A$15)+1;VIND.SPEC("A";$A$9:$A$15)-VIND.SPEC("V";$A$9:$A$15)-1))

maar ik heb er nog een fout instaan
Range van waarden zijn A9:a15

[ Voor 50% gewijzigd door xtrme op 06-09-2018 14:08 ]

[KabouterSuper]

Je kunt een getal die als string getoond wordt ook altijd even met 1 vermenigvuldigen. Dus:
=1*MIDDEN(AB9;2;VIND.SPEC("A";AB9;1)-2)
Voordeel hiervan is dat strings die geen getallen zijn, gelijk een #VALUE worden. Als je deze error afvangt (ALS.FOUT(1*MIDDEN(AB9;2;VIND.SPEC("A";AB9;1)-2);0)
dan kan je ook sommeren.

[dix-neuf]

In mijn bericht van 13.45 uur heb ik kolom A als kolom met gegevens beschouwd, dat moet uiteraard kolom AB zijn.
Als je #WAARDE ook nog wil opvangen voor het geval dat in kolom AB lege cellen voorkomen, dan kun je gebruiken:

code:

=ALS(OF(ISGETAL(AB9);AB9="");"";MIDDEN(AB9;VIND.SPEC("A";AB9)+1;LENGTE(AB9)-VIND.SPEC("A";AB9)))
en
=ALS(OF(ISGETAL(AB9);AB9="");"";MIDDEN(AB9;2;VIND.SPEC("A";AB9)-2))

en voor de optelling van AC9:AC100 (invoeren via Ctrl-Shift-Enter):

=SOM(ALS(AC9:AC100<>"";--AC9:AC100))

[JasperS1965]

Zo, dat is ook weer opgelost!
Nu zit ik met een laatste probleempje: ik zou liever niet de tussenstap moeten nemen om de uitkomst van mijn formule ergens te zetten en dan de som te moeten nemen want het gaat namelijk over een kalender (12 maanden in 12 aparte werkbladen) verdeeld over 26 verschillende personen.
Echter kan ik met volgende formule geen SOM nemen van een bereik (bij ":" zegt excel dat mijn formule een fout bevat) en kan ik enkel ";" gebruiken, maar om dat dan voor alle personen over alle dagen te doen...

Dit is de formule die ik gebruik (nog zonder het niet meerekenen van cellen met enkel getallen):
SOM(--(MIDDEN(AB9;VIND.SPEC("A";AB9;1)+1;LENGTE(AB9)-VIND.SPEC("A";AB9;1))):--(MIDDEN(AD9;VIND.SPEC("A";AD9;1)+1;LENGTE(AD9)-VIND.SPEC("A";AD9;1))))

Dit is natuurlijk maar toegepast over een testbereik en niet wat het normaal zou moeten zijn

[ Voor 6% gewijzigd door JasperS1965 op 07-09-2018 12:25 ]

[dix-neuf]

Invoeren via Ctrl-Shift-Enter:

code:

=SOM(--MIDDEN((AB9:AD9);VIND.SPEC("A";(AB9:AD9))+1;LENGTE((AB9:AD9))-VIND.SPEC("A";(AB9:AD9))))

edit:
het kan met 1 openings- en sluitingshaakje minder (het resultaat verandert daardoor niet):

code:

=SOM(--MIDDEN((AB9:AD9);VIND.SPEC("A";(AB9:AD9))+1;LENGTE(AB9:AD9)-VIND.SPEC("A";(AB9:AD9))))

[ Voor 42% gewijzigd door dix-neuf op 07-09-2018 15:22 ]

[JasperS1965]

dix-neuf schreef op vrijdag 7 september 2018 @ 13:18:
Invoeren via Ctrl-Shift-Enter:

code:

=SOM(--MIDDEN((AB9:AD9);VIND.SPEC("A";(AB9:AD9))+1;LENGTE((AB9:AD9))-VIND.SPEC("A";(AB9:AD9))))

edit:
het kan met 1 openings- en sluitingshaakje minder (het resultaat verandert daardoor niet):

code:

=SOM(--MIDDEN((AB9:AD9);VIND.SPEC("A";(AB9:AD9))+1;LENGTE(AB9:AD9)-VIND.SPEC("A";(AB9:AD9))))

Top! Hoe voeg ik hier aan toe dat er enkel rekening gehouden wordt met cellen die beginnen met een V?

Cellen kunnen bestaan uit: enkel cijfers, VcijferAcijfer of ook ZcijferWcijfer.
Ook voor de waardes achter Z en W zal ik aparte subtotalen moeten maken

[dix-neuf]

Hoe voeg ik hier aan toe dat er enkel rekening gehouden wordt met cellen die beginnen met een V?

Voer in via Ctrl-Shift-Enter:

code:

=SOM(ALS(LINKS(AB9:AD9;1)="V";--MIDDEN((AB9:AD9);VIND.SPEC("A";(AB9:AD9))+1;LENGTE(AB9:AD9)-VIND.SPEC("A";(AB9:AD9)))))

Voor de cellen beginnend met W en Z wijzig je in bovenstaande formule de V in resp. W of Z.

[dix-neuf]

Of wil je de optelling voor V, W, en Z samen?

[JasperS1965]

Neen dat is helemaal goed zo! Bedankt voor alle hulp

[Verwijderd]

Is de "truc" om getallen op te tellen met behulp van de matrix
{ =SOM(--(AC9:AC100))}
ook toe te passen op de functie SUBTOTAAL?

Hoe?

[F_J_K]

Verwijderd schreef op zaterdag 25 juli 2020 @ 14:34:
Is de "truc" om getallen op te tellen met behulp van de matrix
{ =SOM(--(AC9:AC100))}
ook toe te passen op de functie SUBTOTAAL?

Hoe?

Welkom @Verwijderd

Goed gevonden. Maar: start ajb even een eigen topic (Client Software Algemeen en dan de knop rechtsboven), waar je iets meer info geeft over de gewenste uitkomst, eigen pogingen obv. wat je precies wilt, en bv. verwijzing naar dit topic.

Als we hier doorgaan, is er namelijk de levensgrote kans dat mensen gaan nadenken over de originele vraag.

[ Voor 3% gewijzigd door F_J_K op 25-07-2020 14:58 ]