Hoe cellen met tekst en getallen in Excel op te tellen?

Als u een lijst met cellen hebt die zowel tekst als getallen bevatten, kan het lastig zijn om alleen de numerieke waarden op te tellen. U wilt bijvoorbeeld totalen berekenen op basis van specifieke tekstcriteria of gewoon de tekst negeren en de getallen optellen. Deze tutorial introduceert u aan meerdere methoden om dit in Excel te bereiken.

Cellen met tekst en getallen optellen op basis van bepaalde tekst met een formule

Cellen met tekst en getallen optellen op basis van bepaalde tekst met een handige functie

Cellen met tekst en getallen optellen waarbij de tekst wordt genegeerd met een door de gebruiker gedefinieerde functie

Cellen met tekst en getallen optellen op basis van bepaalde tekst met een formule

U kunt een matrixformule gebruiken om de getallen op te tellen op basis van hun corresponderende tekststring binnen de cel. Volg hiervoor de volgende stappen:

1. Schrijf eerst uw tekststrings op die u wilt gebruiken om de bijbehorende getallen in een kolomcellen op te tellen.

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

Opmerking: In de bovenstaande formule is D2 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 naar beneden naar de cellen waarop u deze formule wilt toepassen om het resultaat te krijgen:

Cellen met tekst en getallen optellen op basis van bepaalde tekst met een handige functie

Als de bovenstaande formule moeilijk te begrijpen is, biedt Kutools voor Excel een handig hulpmiddel - Som op basis van dezelfde tekst. Met deze functie kunt u het totaalresultaat in een cel krijgen die gemengd is met getallen en tekst zonder dat u formules hoeft te onthouden.

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

2. Klik vervolgens op Kutools > Formulehulp > Formulehulp, zie screenshot:

3. Voer in het dialoogvenster Formulehulp de volgende bewerkingen uit:

• Selecteer Aantal uit de vervolgkeuzelijst Formuletype;
• In het keuzelijstvenster Selecteer een formule, klik om de optie Som op basis van dezelfde tekst te selecteren;
• Vervolgens, in de Argumentinvoer sectie, selecteer het bereik van cellen dat de tekst en getallen bevat die u wilt optellen in de Bereik tekstvak, en selecteer vervolgens de tekstcel waarop u de waarden wilt optellen in de Tekst tekstvak.
  Tips: In het Tekst tekstvak moet u de standaard absolute celverwijzing wijzigen in een relatieve celverwijzing om de formule correct te slepen.

  

4. Klik vervolgens op de knop OK, het eerste resultaat wordt berekend, sleep dan de vulgreep om de formule naar andere cellen te vullen, zie screenshot:

Cellen met tekst en getallen optellen waarbij de tekst wordt genegeerd met een door de gebruiker gedefinieerde functie

Als u de volgende gegevens heeft die gemengd zijn met tekst en getallen, en u wilt alleen de getallen in elke cel extraheren en optellen. Hoe kunt u dit snel in Excel doen?

Om dit probleem op te lossen, kunt u een door de gebruiker gedefinieerde functie maken. Volg deze stappen:

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

2. Klik op Invoegen > Module, en plak de volgende code in het modulevenster.

VBA-code: tel alleen de getallen op in de cel die getallen en tekst bevat

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 =SomGetallen(A2) (A2 geeft de cel aan die u wilt optellen), sleep dan de vulgreep naar beneden naar de cellen waar u de formule wilt invullen, en alleen de getallen in elke cel worden bij elkaar opgeteld, zie screenshot:

Meer gerelateerde artikelen: