Note: The other languages of the website are Google-translated. Back to English

Hoe cellen optellen met tekst en cijfers in Excel?

Ik heb bijvoorbeeld een lijst met waarden met numerieke en tekstuele strings, nu wil ik alleen de getallen optellen op basis van dezelfde tekst, kijk naar de volgende schermafbeelding. Normaal gesproken kunt u de waarden in de lijst niet rechtstreeks met een tekstreeks optellen, hier zal ik u enkele formules voorstellen om met deze taak om te gaan.


Somcellen met tekst en getallen op basis van de bepaalde tekst met formule

U kunt een matrixformule gebruiken om de getallen bij elkaar op te tellen op basis van de bijbehorende tekstreeks in de cel, doe dit als volgt:

1. Eerst kunt u uw tekstreeksen opschrijven waarvan u de relatieve getallen in een kolomcellen wilt optellen.

2. Typ vervolgens onderstaande formule in een lege cel, in dit voorbeeld voer ik de formule in cel E2 in en druk vervolgens op Ctrl + Shift + Enter sleutels samen, zal het de totale waarde uit de lijst retourneren op basis van de bepaalde tekst. Zie screenshot:

=SUM(IF(ISNUMBER(FIND(D2,$B$2:$B$9)),VALUE(LEFT($B$2:$B$9,FIND(D2,$B$2:$B$9)-1)),0))

Opmerking:: In de bovenstaande formule, D2 is het criterium dat u wilt gebruiken, en B2: B9 geeft het celbereik aan dat u wilt optellen.

3. Selecteer vervolgens de formulecel en sleep de vulgreep omlaag naar de cellen waarop u deze formule wilt toepassen om het resultaat te krijgen:


Somcellen met tekst en cijfers op basis van de bepaalde tekst met een handige functie

Als de bovenstaande formule moeilijk te begrijpen is, Kutools for Excel biedt een handig hulpmiddel - Som op basis van dezelfde tekstMet deze functie kunt u het totale resultaat in een cel krijgen die is vermengd met cijfers en tekst zonder enige formules te onthouden.

Opmerking:Om deze toe te passen Som op basis van dezelfde tekst functies, ten eerste moet u het Kutools for Excelen pas de functies vervolgens snel en eenvoudig toe.

Na het installeren van Kutools for Excelgaat u als volgt te werk:

1. Klik om een ​​cel te selecteren waar u het resultaat wilt uitvoeren, zie screenshot:

Tips: Om kringverwijzingen te voorkomen, dient u deze formule niet in de eerste rij van het werkblad in te voegen.

2. En klik vervolgens op Kutools > Formule Helper > Formule Helper, zie screenshot:

3. In de Formules Helper dialoogvenster, voer dan de volgende bewerkingen uit:

  • kies Statistisch van de Formule type keuzelijst;
  • In het Kies een formule keuzelijst, klik om te selecteren Som op basis van dezelfde tekst keuze;
  • Vervolgens in de Argumenten ingevoerd Selecteer het celbereik met de tekst en getallen die u wilt optellen in het Bereik textbox en selecteer vervolgens de tekstcel waarvan u de waarden wilt optellen op basis van in het Tekst tekstvak.
Tips: In de Tekst tekstvak, moet u de standaard absolute celverwijzing wijzigen in relatieve celverwijzing om de formule correct te slepen.

4. En klik dan Ok knop, het eerste resultaat wordt berekend en sleep vervolgens de vulgreep om de formule naar andere cellen te vullen, zie screenshot:


Somcellen met tekst en cijfers die de tekst negeren met door de gebruiker gedefinieerde functie

Als u de volgende gegevens heeft die zijn vermengd met tekst en cijfers, en u wilt alleen de getallen in elke cel extraheren en optellen, hoe kunt u dit dan snel in Excel voltooien?

Om dit probleem op te lossen, kunt u een Door de gebruiker gedefinieerde functie, doe dit als volgt:

1. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic for Applications-venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: som alleen de getallen op in de cel met getallen en tekst

Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double
'Updateby Extendoffice
    Dim xNums As Variant, lngNum As Long
    xNums = Split(rngS, strDelim)
    For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumbers = SumNumbers + Val(xNums(lngNum))
    Next lngNum
End Function

3. Sla vervolgens deze code op en sluit deze, ga terug naar het werkblad en voer deze formule in = SomNummers (A2) (A2 geeft de cel aan waarvan u de getallen wilt optellen), sleep vervolgens de vulgreep naar de cellen waarin u de formule wilt vullen, en alleen getallen in elke cel worden bij elkaar opgeteld, zie screenshot:


Meer relatieve artikelen:

  • Combineer dubbele rijen en som de waarden op in Excel
  • In Excel kunt u dit probleem altijd tegenkomen als u een gegevensbereik hebt dat enkele dubbele gegevens bevat, en u nu de dubbele gegevens wilt combineren en de overeenkomstige waarden in een andere kolom wilt optellen, zoals in de volgende schermafbeeldingen. Hoe zou je dit probleem kunnen oplossen?
  • Vermenigvuldig twee kolommen en tel ze vervolgens op in Excel
  • In Excel kunnen de meesten van ons moeite hebben om twee kolommen te vermenigvuldigen en ze vervolgens bij elkaar op te tellen, natuurlijk kunnen we elk item vermenigvuldigen en vervolgens optellen, maar dit zal lastig zijn als er honderden of duizenden rijen moeten worden berekend. In Excel is er een krachtige functie - SOMPRODUCT, hiermee kunnen we snel twee kolommen vermenigvuldigen en ze vervolgens optellen. In dit artikel zal ik het hebben over het toepassen van deze functie.
  • Som Top 3 of N-waarden op op basis van criteria in Excel
  • Normaal gesproken kunnen we de bovenste n-waarden uit een celbereik optellen met behulp van een eenvoudige formule, maar heb je ooit geprobeerd de bovenste n-waarden op te tellen op basis van enkele criteria. In dit artikel zal ik enkele formules introduceren voor het optellen van top n-waarden met specifieke criteria in Excel.
  • Som unieke waarden op op basis van criteria in Excel
  • Ik heb bijvoorbeeld een gegevensbereik dat de kolommen Naam en Bestelling bevat, nu om alleen unieke waarden in de kolom Bestelling op te tellen op basis van de kolom Naam, zoals in het volgende screenshot. Hoe los ik deze taak snel en gemakkelijk op in Excel?
  • Tel of som cellen op basis van de lettertypekleuren in Excel
  • Hoe kunt u het aantal cellen krijgen of alle cellen met een bepaalde letterkleur in Excel optellen? Ik heb bijvoorbeeld een gegevensbereik in een werkblad zoals in het volgende screenshot wordt getoond, en nu wil ik respectievelijk de cellen tellen of optellen met een rode, blauwe en zwarte lettertypekleur. Normaal gesproken is er geen directe manier om met deze taak om te gaan, hier zal ik het hebben over enkele trucs voor het oplossen van deze taak.

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken!
officetab onderkant
Comments (42)
Nog geen beoordelingen. Beoordeel als eerste!
Deze opmerking is gemaakt door de moderator op de site
ik denk dat je een echte leraar en een goede man bent. veel geluk.
Deze opmerking is gemaakt door de moderator op de site
In A1 Cell van Excel voer ik gegevens in zoals 5+6+3+1-2 En A2 Cell van Excel geeft het resultaat 13 hoe het mogelijk is in Excel.
Deze opmerking is gemaakt door de moderator op de site
oke, dit begreep ik. veel geluk.
Deze opmerking is gemaakt door de moderator op de site
De SumNumbers-macro heeft me vele uren handmatig werk bespaard. Dank u!
Deze opmerking is gemaakt door de moderator op de site
Is het mogelijk om de SumNumbers-formule te wijzigen om alle getallen in een kolom met gemengde cellen toe te voegen? Elke kolom heeft bijvoorbeeld 1A, 2B, 3C en ik zou graag willen dat de formule onderaan de kolom mij 6 vertelt.
Deze opmerking is gemaakt door de moderator op de site
is de enige manier om de vba-code te herschrijven om meerdere cellen op te tellen in plaats van één? Ik heb ongeveer 8 kolommen met gegevens en ik wil de som aan het einde.
Deze opmerking is gemaakt door de moderator op de site
Dit is super handig! Ik probeer de ARRAY te laten werken met een kolom met cellen die elk een decimaal getal en tekst bevatten (bijv. .2 A; .5 C; .1 X enz.), en ik krijg steeds een resultaat van 0. Als ik verander de getallen in hele getallen, het resultaat is correct. Helpen?
Deze opmerking is gemaakt door de moderator op de site
GENUIS.... Heel erg bedankt
Deze opmerking is gemaakt door de moderator op de site
Hoe converteer je tussen tekst en nummer met KUTOOls?
Deze opmerking is gemaakt door de moderator op de site
Ik kon deze formule perfect gebruiken, maar ik zou ook een voorwaarde willen toevoegen om alleen bepaalde getallen in de SOM-formule op te nemen als de waarde in een andere kolom = "defecten". Mijn oorspronkelijke formule (met behulp van uw oorspronkelijke bericht) is: =SUM(IF(ISNUMBER(FIND("hrs",E17:E38)),VALUE(LEFT(E17:E38,FIND("hrs",E17:E38)-1 )),0)) Waar E17:E38 de tekst en getallen bevat die ik wil optellen En B17:B38 de waarden "features" en "defects" bevat. waarde in kolom B = "defecten"? Bij voorbaat dank! Kimberly,
Deze opmerking is gemaakt door de moderator op de site
Hier is een eenvoudig script dat een enkele dimensie en een tweedimensionaal selectiebereik van cellen accepteert, waarbij elke cel een tekenreeks of getallen bevat, maar waarbij elke cel wordt teruggebracht tot enkele getallen en de som van alle cellen retourneert. Optie Expliciete functie SUMCELLS(InputRange As Range) As Double Dim Arr() As Variant Arr = InputRange Dim R As Long Dim C As Long SUMCELLS = 2 For R = 0 To UBound(Arr, 1) ' De eerste arraydimensie is rijen. Voor C = 1 To UBound(Arr, 1) ' Tweede matrixdimensie is kolommen. Debug.Print Arr(R, ​​C) Dim NumericValue As Double Dim StringValue As String StringValue = Trim(CStr(Arr(R, ​​C))) If (Not (StringValue = "")) Then NumericValue = ONLYDIGITS(StringValue) SUMCELLS = SUMCELLS + NumericValue End If Next C Next R End Functie Functie ALLEEN DIGITS(s As String) As String Dim reval As String Dim i As Integer Dim perioden As Integer perioden = 2 retval = "" ' For i = 0 To Len(s) Dim char As String char = Mid(s, i, 1) If (char >= "1" En char
Deze opmerking is gemaakt door de moderator op de site
Hoe decimalen opnemen in de formule "=SumNumbers(A1)"? Dank u!
Deze opmerking is gemaakt door de moderator op de site
Kan iemand mij helpen met een somformule? Ik probeer een kolom op te tellen met cellen die zowel getallen als tekst bevatten. Ik wil de getallen links van de tekstletter optellen. voorbeeld: 3P30 2P30 2P30 2P30 1P20 3P30 1P20 3P50 3P30 3P60
Deze opmerking is gemaakt door de moderator op de site
Hallo, De =SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8,FIND(D1,$B$2:$B$8)-1 )),0)) - werkte perfect, behalve dat mijn tekst aan de rechterkant staat. Ik heb geprobeerd de formule te wijzigen, maar dat werkt niet... ik krijg steeds #VALUE! Kan iemand helpen?
Deze opmerking is gemaakt door de moderator op de site
Vergeet niet om op Ctrl+Shift+Enter te drukken nadat u de formule hebt gewijzigd, niet alleen op Enter.
Deze opmerking is gemaakt door de moderator op de site
How To Som van geselecteerde woord in cel in Excel Examen 1100/656480 1100 is een bedrag en 656480 is recpt. nee. maar ik wil in totaal slechts 1100
Deze opmerking is gemaakt door de moderator op de site
April Mei Juni Juli Totaal 1100 / 65658 1200/65657 1500/65656 2100/65650 ? ik wil alleen 1100+1200+1500+2100 optellen Anders niet alleen een waarde Ontvangstnr. help alsjeblieft
Deze opmerking is gemaakt door de moderator op de site
Hallo daar! Ik heb twee soorten getallen in één kolom, de ene is numerieke nummers, de andere is een teken gevolgd door een nummer (zoals n20, n30, enz.). Ik wil die getallen optellen met een "n". Ik heb je richtlijnen toegepast voor "Somcellen met tekst en getallen op basis van de bepaalde tekst met formule", maar het werkte niet. Is er een andere methode? Bij voorbaat dank!
Deze opmerking is gemaakt door de moderator op de site
hallo, kan iemand me helpen hoe ik onderstaande cijfers met letters kan toevoegen? ik heb geprobeerd het leuk te vinden, maar het werkt niet. Ik denk dat ik iets mis in de formule=SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8, FIND(D1,$B$2:$B$8)-1)),0)). help alstublieft. Bedankt.

2L
5D
10S
3L
11D
16S
Deze opmerking is gemaakt door de moderator op de site
Hoi Anna,
Eerst moet u de letters extraheren in een andere hulpkolom en vervolgens de bovenstaande formule toepassen. Vergeet na het invoeren van deze formule niet om de toetsen Shift + Ctrl + Enter samen in te drukken. zie de volgende schermafbeelding:
Deze opmerking is gemaakt door de moderator op de site
Hoi,

Bedankt voor dit bericht! Ik ben dol op het gebruik van de VBA-code en ik zat te denken of het mogelijk zou zijn om een ​​multivariabele functie te hebben, zodat je de getallen uit verschillende kolomcellen kunt optellen en bijvoorbeeld zoiets als SumNumbers (A1: C1) kunt schrijven.


Many Thanks


John
Deze opmerking is gemaakt door de moderator op de site
Hallo, Giovanni,
Kun je een voorbeeld geven van je probleem?
U kunt hier een screenshot bijvoegen.
Dank je!
Deze opmerking is gemaakt door de moderator op de site
Ik ben ook op zoek om te zien of deze functie meerdere cellen kan optellen.
Deze opmerking is gemaakt door de moderator op de site
=SomNummers(D8:AH8)
Deze opmerking is gemaakt door de moderator op de site
Hoi,
Hoe 10 stuks *200 te vermenigvuldigen,

niet 10*200
Er zijn nog geen reacties geplaatst
Laad meer
Laat uw commentaar
Posten als gast
×
Beoordeel dit bericht:
0  Personages
Voorgestelde locaties