Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
S'inscrire  \/ 
x

or

Comment additionner des cellules avec du texte et des nombres dans Excel?

Par exemple, j'ai une liste de valeurs contenant des chaînes numériques et textuelles, maintenant, je veux additionner uniquement les nombres basés sur le même texte, regardez la capture d'écran suivante. Normalement, vous ne pouvez pas additionner directement les valeurs de la liste avec une chaîne de texte, ici, je vais vous présenter quelques formules pour gérer cette tâche.


Somme des cellules contenant du texte et des nombres en fonction du texte avec une formule

Vous pouvez utiliser une formule matricielle pour additionner les nombres en fonction de leur chaîne de texte correspondante dans la cellule, procédez comme suit:

1. Tout d'abord, vous pouvez écrire vos chaînes de texte que vous souhaitez additionner les nombres relatifs dans les cellules d'une colonne.

2. Tapez ensuite la formule ci-dessous dans une cellule vide, dans cet exemple, je vais entrer la formule dans la cellule E2, puis appuyez sur Ctrl + Shift + Enter clés ensemble, il renverra la valeur totale de la liste en fonction du texte donné. Voir la capture d'écran:

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

Notes: Dans la formule ci-dessus, D2 est le critère que vous souhaitez utiliser, et B2: B9 indique la plage de cellules que vous souhaitez additionner.

3. Sélectionnez ensuite la cellule de formule et faites glisser la poignée de recopie vers les cellules auxquelles vous souhaitez appliquer cette formule pour obtenir le résultat:


Somme des cellules contenant du texte et des nombres en fonction du texte donné avec une fonctionnalité pratique

Si la formule ci-dessus vous est difficile à comprendre, Kutools pour Excel fournit un outil utile - Somme basée sur le même texte, avec cette fonctionnalité, vous pouvez obtenir le résultat total dans une cellule mélangée avec des nombres et du texte sans vous souvenir de formules.

A Noter:Pour appliquer ces Somme basée sur le même texte fonctionnalités, tout d'abord, vous devez télécharger le Kutools pour Excel, puis appliquez les fonctionnalités rapidement et facilement.

Après l'installation de Kutools pour Excel, procédez comme suit:

1. Cliquez pour sélectionner une cellule dans laquelle vous souhaitez afficher le résultat, voir capture d'écran:

Conseils: Pour éviter les références circulaires, veuillez ne pas insérer cette formule dans la première ligne de la feuille de calcul.

2. Et puis cliquez Kutools > Aide à la formule > Aide à la formule, voir capture d'écran:

3. Dans le Aide aux formules boîte de dialogue, veuillez effectuer les opérations suivantes:

  • Choisir Statistique du Type de formule la liste déroulante;
  • Dans le Choisissez une formule zone de liste, cliquez pour sélectionner Somme basée sur le même texte l'option;
  • Ensuite, dans le Entrée d'arguments section, sélectionnez la plage de cellules contenant le texte et les nombres que vous souhaitez additionner dans le Autonomie zone de texte, puis sélectionnez la cellule de texte sur laquelle vous souhaitez additionner les valeurs dans le Texte zone de texte.
Conseils: Dans le Texte zone de texte, vous devez changer la référence de cellule absolue par défaut en référence de cellule relative pour faire glisser la formule correctement.

4. Et puis, cliquez Ok bouton, le premier résultat sera calculé, puis faites glisser la poignée de remplissage pour remplir la formule vers d'autres cellules, voir capture d'écran:


Somme des cellules contenant du texte et des nombres en ignorant le texte avec la fonction définie par l'utilisateur

Si vous avez les données suivantes qui sont mélangées avec du texte et des nombres, et que vous voulez juste extraire et additionner uniquement les nombres dans chaque cellule, comment pourriez-vous terminer rapidement cela dans Excel?

Pour résoudre ce problème, vous pouvez créer un Fonction définie par l'utilisateur, veuillez faire comme ceci:

1. Maintenez le ALT + F11 clés pour ouvrir le Fenêtre Microsoft Visual Basic pour Applications.

2. Cliquez insérer > Moduleet collez le code suivant dans le Fenêtre du module.

Code VBA: additionnez uniquement les nombres dans la cellule contenant les nombres et le texte

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. Ensuite, enregistrez et fermez ce code, revenez à la feuille de calcul et entrez cette formule = SumNumbers (A2) (A2 indique la cellule dans laquelle vous souhaitez additionner les nombres), puis faites glisser la poignée de recopie vers les cellules que vous souhaitez remplir la formule, et seuls les nombres de chaque cellule sont additionnés, voir capture d'écran:


Articles plus relatifs:

  • Combinez des lignes en double et additionnez les valeurs dans Excel
  • Dans Excel, vous pouvez toujours rencontrer ce problème, lorsque vous avez une plage de données qui contient des entrées en double et que vous souhaitez maintenant combiner les données en double et additionner les valeurs correspondantes dans une autre colonne, comme illustré ci-dessous. Comment pourriez-vous résoudre ce problème?
  • Multipliez deux colonnes, puis additionnez dans Excel
  • Dans Excel, la plupart d'entre nous peuvent souffrir de multiplier deux colonnes, puis de les additionner, bien sûr, nous pouvons multiplier chaque élément puis les additionner, mais ce sera gênant s'il y a des centaines ou des milliers de lignes à calculer. Dans Excel, il existe une fonction puissante - SUMPRODUCT, avec elle, nous pouvons rapidement multiplier deux colonnes, puis les additionner. Cet article, je vais parler de la façon d'appliquer cette fonction.
  • Sommez les 3 ou N valeurs principales basées sur des critères dans Excel
  • Normalement, nous pouvons additionner les n premières valeurs d'une plage de cellules en utilisant une formule simple, mais avez-vous déjà essayé de additionner les n premières valeurs en fonction de certains critères. Cet article, je vais présenter quelques formules pour additionner les n premières valeurs avec des critères spécifiques dans Excel.
  • Somme des valeurs uniques basées sur des critères dans Excel
  • Par exemple, j'ai une plage de données qui contient les colonnes Nom et Ordre, maintenant, pour additionner uniquement les valeurs uniques dans la colonne Ordre en fonction de la colonne Nom comme illustré ci-dessous. Comment résoudre cette tâche rapidement et facilement dans Excel?
  • Compter ou additionner les cellules en fonction des couleurs de police dans Excel
  • Comment pouvez-vous obtenir le nombre de cellules ou additionner toutes les cellules contenant une couleur de police particulière dans Excel? Par exemple, j'ai une plage de données dans une feuille de calcul comme illustré ci-dessous, et maintenant je veux respectivement compter ou additionner les cellules qui ont une couleur de police rouge, bleue et noire. Normalement, il n'y a pas de moyen direct de traiter cette tâche, ici, je vais parler de quelques astuces pour résoudre ce travail.

Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Alexander · 1 months ago
    How to do this, but with decimal places? When using decimals, it returns 0...

  • To post as a guest, your comment is unpublished.
    NITISH · 6 months ago
    HOW TO MULTIPLY   COLUM 1   , 2 PCS   , COLOUM 2 RATE 1 USD   TOTAL AMOUNT  ?
  • To post as a guest, your comment is unpublished.
    mariafayyaz1992@gmail.com · 10 months ago
    kindly reply to "I want to use this formula to sum only numbers in all columns like =SumNumbers(A1:G1) but it gives error VALUE. how it can be fixed?"
    • To post as a guest, your comment is unpublished.
      Justin · 8 months ago
      You need to use the VBA for that to work.  the VBA code defines the SumNumbers as a function.
      • To post as a guest, your comment is unpublished.
        mariafayyaz1992@gmail.com · 7 months ago
        I have used VBA for that but it doesn't work. Can you plz take a look?
      • To post as a guest, your comment is unpublished.
        fcg · 7 months ago
        I have used VBA for that but it does not work. Can you take a look plz?
  • To post as a guest, your comment is unpublished.
    mariafayyaz1992@gmail.com · 11 months ago
    I want to use this formula to sum only numbers in all columns like =SumNumbers(A1:G1) but it gives error VALUE. how it can be fixed?
  • To post as a guest, your comment is unpublished.
    joe · 1 years ago
    I am using this in excel 2016 but it doesn't seem to be working is there a work through? i keep getting a "
    #name?" error
  • To post as a guest, your comment is unpublished.
    louisjoshua · 1 years ago
    Hi,

    I have a question. How to get the sum or the average of the column that has text inside that column. Please see attachment. Your answer would be greatly appreciated.
  • To post as a guest, your comment is unpublished.
    Ronak · 1 years ago
    Apple 10 (+)
    Apple 5 (-)
    Apple 20 (+)
    Apple 15 (+)
    Apple 30 (+)
    Apple 25 (-)


    Can anyone help solve this?
  • To post as a guest, your comment is unpublished.
    Maarten · 2 years ago
    I have a variant of this problem you solved with the SumNumbers module. The issue is that the cell contains both dollar values and other numbers, for example:
    "Case of Soda (354mL x 24): USD 25.00 Case of Water (0.500L x 24): USD 15.00"

    Using the SumNumbers module adds up all numbers but we only want the dollar values.
  • To post as a guest, your comment is unpublished.
    Z · 2 years ago
    If you have the following data which is mixed with text and numbers, and you just want to extract and add up only the numbers in each cell, how could you quickly finish this in Excel?.

    Bro, but I only want to sum specific number after specific word? can I do that?
  • To post as a guest, your comment is unpublished.
    Yuri · 2 years ago
    Thank you very much!!!
  • To post as a guest, your comment is unpublished.
    jasmeet · 2 years ago
    In this formula =SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8,FIND(D1,$B$2:$B$8)-1)),0)) can be put a range in place of D1 - say D1:D5 - not getting result, if i put it this way. What is the correct way to do this?
  • To post as a guest, your comment is unpublished.
    npanamaldeniya@ryerson.ca · 2 years ago
    Hi, I want to be able to add a number and also text to one cell (C5 all the way to C16) then get only the numbers in there to add up. But the =SumNumbers(A1) formula doesn't seem to work.
  • To post as a guest, your comment is unpublished.
    Emmy du Plooy · 3 years ago
    Hi, I hope someone can help. I like to add inside the cell +100j+50f+600m but must display/add on cell as 750 only. So you see 750 in cell but when press F2, it will show =100j+50f+600m
  • To post as a guest, your comment is unpublished.
    David · 3 years ago
    Hi. Please can someone help me. I am trying to SUM a column in EXCEL that has numbers and letters. eg 8.1km, 10.5km etc. There are 1031 lines or cells in the column. I am using this formula =SUM(LEFT(D3),LEN(D3)-2+LEFT(D4),LEN(D4)-2) which works if i want to add 2 or 3 cells together but i cant use this for 1031 cells. i wanted to add a : in place of the + but it comes back with an error and will not allow me to create the formula. Any help will be greatly appreciated
  • To post as a guest, your comment is unpublished.
    Mrityunjay · 4 years ago
    Hi,
    How to multiply 10 pcs *200,

    not 10*200
  • To post as a guest, your comment is unpublished.
    Giovanni · 4 years ago
    Hi,

    Thank you for this post! I am keen on using the VBA code and I was thinking if it would be possible to have a multivariable function so that you can sum the numbers from different column cells and write something like SumNumbers(A1:C1), for example.


    Many Thanks


    Giovanni
    • To post as a guest, your comment is unpublished.
      skyyang · 4 years ago
      Hello, Giovanni,
      Can you give an example of your problem?
      You can attach a screenshot here.
      Thank you!
      • To post as a guest, your comment is unpublished.
        kaydenar · 4 years ago
        I am also looking to see if this function can sum multiple cells.
        • To post as a guest, your comment is unpublished.
          kaydenar · 4 years ago
          =SumNumbers(D8:AH8)
  • To post as a guest, your comment is unpublished.
    Anna · 4 years ago
    hi can anyone help me how to add below numbers with letters? i tried to do like but it doest work i think i miss something in the formula=SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8,FIND(D1,$B$2:$B$8)-1)),0)). please help.Thanks.

    2L
    5D
    10S
    3L
    11D
    16S
    • To post as a guest, your comment is unpublished.
      skyyang · 4 years ago
      Hello, Anna,
      First, you should extract the letters into another helper column, and then apply the above formula, after entering this formula, please remember to press Shift + Ctrl + Enter keys together. see the following screenshot image:
  • To post as a guest, your comment is unpublished.
    JudyW · 4 years ago
    Hi There! I have two types of numbers in one column, one is numeric numbers, the other is a character followed by a number (such as n20, n30, etc). I wish to add up those numbers with an "n". I applied your guidance for "Sum Cells Containing Text And Numbers Based On The Certain Text With Formula", but it did not work. Is there any other method? Thanks in advance!
  • To post as a guest, your comment is unpublished.
    Ravi Sharma · 4 years ago
    April May June July Total
    1100 / 65658 1200/65657 1500/65656 2100/65650 ?

    i want to total only 1100+1200+1500+2100
    Other not a value only Receipt no. please help any
  • To post as a guest, your comment is unpublished.
    Ravi Sharma · 4 years ago
    How To Sum of selected word in cell in excel

    Exam

    1100/656480

    1100 is a amount and 656480 is recpt. no. but i want to total only 1100
  • To post as a guest, your comment is unpublished.
    Laura · 4 years ago
    Hello,
    The =SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8,FIND(D1,$B$2:$B$8)-1)),0)) - worked perfectly, except that my text is on the right.
    I tried to change the formula however that is not working... i just keep getting #VALUE!

    Can anyone help?
    • To post as a guest, your comment is unpublished.
      LeRoy · 4 years ago
      Remember to press Ctrl+Shift+Enter after changing the formula, not just Enter.
  • To post as a guest, your comment is unpublished.
    Colt · 4 years ago
    Can anyone help me with a sum formula?
    I'm trying to sum a column with cells that contain both numbers and text.
    I want to sum the numbers to the left of the text letter. sample:
    3P30
    2P30
    2P30
    2P30
    1P20
    3P30
    1P20
    3P50
    3P30
    3P60
  • To post as a guest, your comment is unpublished.
    Angela · 4 years ago
    How to include decimals in the "=SumNumbers(A1)" formula?
    Thank you!
  • To post as a guest, your comment is unpublished.
    Gil Michael Regalado · 4 years ago
    Here's a simple script that accepts single dimension and 2 dimensional selection range of cells with each cell containing string or numbers but with each cell reduced to single numbers and returns the sum of all cells.


    Option Explicit

    Function SUMCELLS(InputRange As Range) As Double
    Dim Arr() As Variant
    Arr = InputRange
    Dim R As Long
    Dim C As Long
    SUMCELLS = 0

    For R = 1 To UBound(Arr, 1) ' First array dimension is rows.
    For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
    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 Function

    Function ONLYDIGITS(s As String) As String
    Dim retval As String
    Dim i As Integer

    Dim periods As Integer
    periods = 0

    retval = ""
    '
    For i = 1 To Len(s)
    Dim char As String
    char = Mid(s, i, 1)
    If (char >= "0" And char
  • To post as a guest, your comment is unpublished.
    Kimberly Koester · 4 years ago
    I was able to use this formula perfectly, but I would also like to add a condition to only include certain numbers in the SUM formula if the value in another column = "defects".

    My original formula (using your original post) is:
    =SUM(IF(ISNUMBER(FIND("hrs",E17:E38)),VALUE(LEFT(E17:E38,FIND("hrs",E17:E38)-1)),0))

    Where E17:E38 contains the text and numbers that I want to sum
    And B17:B38 contains the values "features" and "defects"

    How can I adjust the formula to only SUM those numbers in column E if the value in column B = "defects"?

    Thanks in advance!

    Kimberly,
  • To post as a guest, your comment is unpublished.
    fazlul haque · 5 years ago
    How to convert between text and number using KUTOOls ?
  • To post as a guest, your comment is unpublished.
    MIKE · 5 years ago
    GENUIS.... Thanks ever so much
  • To post as a guest, your comment is unpublished.
    Jenny · 5 years ago
    This is super helpful! I'm trying to get the ARRAY to work with a column of cells that each contains a decimal number and text (e.g. .2 A; .5 C; .1 X etc), and I keep getting a result of 0. If I change the numbers to whole numbers, the result is correct. Help?
  • To post as a guest, your comment is unpublished.
    kc · 5 years ago
    is the any way to rewrite the vba code to sum multi cells instead of one? I have about 8 columns of data and i want the sum at the end.
  • To post as a guest, your comment is unpublished.
    Paul Burchfield · 5 years ago
    Is it possible to change the SumNumbers formula to add all the numbers in a column of mixed cells? For example, each column has 1A, 2B, 3C and I would like the formula at the bottom of the column to tell me 6.
  • To post as a guest, your comment is unpublished.
    Madelene · 5 years ago
    The SumNumbers macro saved me many hours of manual work. Thank you!
  • To post as a guest, your comment is unpublished.
    sadekul islam · 6 years ago
    ok, i understood this. good luck.
  • To post as a guest, your comment is unpublished.
    tawhid · 6 years ago
    In A1 Cell of excel I input data as like 5+6+3+1-2
    And A2 Cell of excel give the Result 13
    how it possible in excel.
  • To post as a guest, your comment is unpublished.
    sadekul islam · 6 years ago
    i think you are real teacher and good man. good luck.