Vai al contenuto principale

Come sommare le celle con testo e numeri in Excel?

Ad esempio, ho un elenco di valori contenenti stringhe numeriche e di testo, ora, voglio sommare solo i numeri in base allo stesso testo, guarda lo screenshot seguente. Normalmente, non puoi sommare i valori nell'elenco direttamente con una stringa di testo, qui ti presenterò alcune formule per affrontare questa attività.


Somma le celle contenenti testo e numeri in base a un determinato testo con formula

È possibile utilizzare una formula di matrice per sommare i numeri in base alla stringa di testo corrispondente all'interno della cella, eseguire le seguenti operazioni:

1. Per prima cosa puoi scrivere le stringhe di testo che desideri sommare i numeri relativi nelle celle di una colonna.

2. Quindi digitare sotto la formula in una cella vuota, in questo esempio, inserirò la formula nella cella E2, quindi premerò Ctrl + Shift + Enter chiavi insieme, restituirà il valore totale dall'elenco in base a un determinato testo. Vedi screenshot:

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

Note:: Nella formula sopra, D2 è il criterio che desideri utilizzare e B2: B9 indica l'intervallo di celle che desideri sommare.

3. Quindi seleziona la cella della formula e trascina il quadratino di riempimento verso il basso sulle celle in cui desideri applicare questa formula per ottenere il risultato:


Somma le celle contenenti testo e numeri in base a un determinato testo con una comoda funzionalità

Se la formula sopra è difficile da capire, Kutools for Excel fornisce uno strumento utile - Somma basata sullo stesso testo, con questa funzione, puoi ottenere il risultato totale all'interno di una cella che si mescola con numeri e testo senza ricordare alcuna formula.

Nota:Per applicarli Somma basata sullo stesso testo caratteristiche, in primo luogo, dovresti scaricare il file Kutools for Excele quindi applicare le funzionalità in modo rapido e semplice.

Dopo l'installazione Kutools for Excel, si prega di fare come segue:

1. Fare clic per selezionare una cella in cui si desidera visualizzare il risultato, vedere screenshot:

Suggerimenti:: Per evitare riferimenti circolari, non inserire questa formula nella prima riga del foglio di lavoro.

2. E poi clicca Kutools > formula Helper > formula Helper, vedi screenshot:

3. Nel Formule Helper finestra di dialogo, eseguire le seguenti operazioni:

  • Seleziona Statistico dal Tipo di formula menu `A tendina;
  • Nel Scegli una formula casella di riepilogo, fare clic per selezionare Somma basata sullo stesso testo opzione;
  • Quindi, nel Input di argomenti sezione, seleziona l'intervallo di celle contenente il testo ei numeri che desideri sommare nel file Escursione casella di testo, quindi seleziona la cella di testo su cui desideri sommare i valori in base al file Testo casella di testo.
Suggerimenti:: Nel Testo casella di testo, è necessario modificare il riferimento di cella assoluto predefinito in riferimento di cella relativo per trascinare correttamente la formula.

4. Quindi fare clic su Ok pulsante, verrà calcolato il primo risultato, quindi trascina la maniglia di riempimento per riempire la formula su altre celle, vedi screenshot:


Somma le celle contenenti testo e numeri ignorando il testo con la funzione definita dall'utente

Se hai i seguenti dati che sono mescolati con testo e numeri e vuoi solo estrarre e sommare solo i numeri in ogni cella, come potresti finire rapidamente questo in Excel?

Per affrontare questo problema, puoi creare un file Funzione definita dall'utente, per favore fai come questo:

1. Tieni premuto il ALT + F11 chiavi per aprire il Finestra di Microsoft Visual Basic, Applications Edition.

2. Clic inserire > Modulie incolla il codice seguente nel file Finestra del modulo.

Codice VBA: somma solo i numeri nella cella contenente numeri e testo

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. Quindi salva e chiudi questo codice, torna al foglio di lavoro e inserisci questa formula = SumNumbers (A2) (A2 indica la cella in cui si desidera sommare i numeri), quindi trascinare il quadratino di riempimento verso il basso sulle celle in cui si desidera riempire la formula e solo i numeri in ogni cella vengono aggiunti insieme, vedere screenshot:


Articoli più relativi:

  • Combina righe duplicate e somma i valori in Excel
  • In Excel , potresti sempre incontrare questo problema, quando hai un intervallo di dati che contiene alcune voci duplicate, e ora vuoi combinare i dati duplicati e sommare i valori corrispondenti in un'altra colonna, come mostrato nelle seguenti schermate. Come hai potuto risolvere questo problema?
  • Moltiplica due colonne e poi somma in Excel
  • In Excel, la maggior parte di noi potrebbe soffrire di moltiplicare due colonne e quindi sommarle, ovviamente, possiamo moltiplicare ogni elemento e quindi sommarli, ma questo sarà problematico se ci sono centinaia o migliaia di righe da calcolare. In Excel, c'è una potente funzione: SUMPRODUCT, con essa possiamo moltiplicare rapidamente due colonne e quindi sommarle. In questo articolo, parlerò di come applicare questa funzione.
  • Sommare i primi 3 o N valori in base ai criteri in Excel
  • Normalmente, possiamo sommare i primi n valori da un intervallo di celle utilizzando una formula semplice, ma hai mai provato a sommare i primi n valori in base ad alcuni criteri. In questo articolo, introdurrò alcune formule per sommare i primi n valori con criteri specifici in Excel.
  • Sommare i valori univoci in base ai criteri in Excel
  • Ad esempio, ho un intervallo di dati che contiene le colonne Nome e Ordine, ora, per sommare solo valori univoci nella colonna Ordine in base alla colonna Nome come mostrato nella seguente schermata. Come risolvere questa attività rapidamente e facilmente in Excel?
  • Conta o somma le celle in base ai colori dei caratteri in Excel
  • Come hai potuto ottenere il numero di celle o sommare tutte le celle che contengono un particolare colore di carattere in Excel? Ad esempio, ho un intervallo di dati in un foglio di lavoro come mostrato nella seguente schermata e ora voglio rispettivamente contare o sommare le celle che hanno un colore del carattere rosso, blu e nero. Normalmente, non esiste un modo diretto per affrontare questo compito, qui parlerò di alcuni trucchi per risolvere questo lavoro.

I migliori strumenti per la produttività in ufficio

Funzioni popolari: Trova, evidenzia o identifica i duplicati   |  Elimina righe vuote   |  Combina colonne o celle senza perdere dati   |   Round senza formula ...
Super ricerca: VLookup a criteri multipli    VLookup a valori multipli  |   VLookup su più fogli   |   Ricerca fuzzy ....
Elenco a discesa avanzato: Crea rapidamente un elenco a discesa   |  Elenco a discesa dipendente   |  Elenco a discesa a selezione multipla ....
Gestore di colonna: Aggiungi un numero specifico di colonne  |  Sposta colonne  |  Attiva/disattiva lo stato di visibilità delle colonne nascoste  |  Confronta intervalli e colonne ...
Funzionalità in primo piano: Messa a fuoco della griglia   |  Vista di progettazione   |   Grande barra delle formule    Gestore di cartelle di lavoro e fogli   |  Resource Library (Testo automatico)   |  Date picker   |  Combina fogli di lavoro   |  Crittografa/decrittografa le celle    Invia e-mail per elenco   |  Super filtro   |   Filtro speciale (filtro grassetto/corsivo/barrato...) ...
I 15 migliori set di strumenti12 Testo Strumenti (aggiungi testo, Rimuovi personaggi, ...)   |   Più di 50 Grafico Tipi (Diagramma di Gantt, ...)   |   40+ Pratico Formule (Calcola l'età in base al compleanno, ...)   |   19 Inserimento Strumenti (Inserisci il codice QR, Inserisci immagine dal percorso, ...)   |   12 Conversione Strumenti (Numeri in parole, Conversione di valuta, ...)   |   7 Unisci e dividi Strumenti (Combina righe avanzate, Celle divise, ...)   |   ... e altro ancora

Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo.  Fai clic qui per ottenere la funzionalità di cui hai più bisogno...

scheda kte 201905


Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro

  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
Comments (50)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi,
I want to have a formula that count text. But what I want is that he count a criterium/ text like as 0,5 and not as 1. Like for example I have one column with T, TO and TM. T I want to count as 1 and TO/ TM as 0,50. I have tried several things, but can not solve it. Hopefully can someone help me out?
This comment was minimized by the moderator on the site
Hi, I am trying to check approximately 8 different types of leave. I am using conditional formatting to colour code letters and then calculate as numbers. It works well, I like it. I did not factor in a half day.

Can anyone tell me how to account for a half day, and still have Excel recognize the Alpha symbol.
This comment was minimized by the moderator on the site
Hi, how do I sum something like the following:
Column A
Row 1 2D
Row 2 2N
Row 3 1D+2N

I have no issues summing the Ds but when I try summing the Ns I would encounter a problem. The string 1D+2N is the culprit, so how do I go about this?
Any help is very much appreciated.

Thank you.
This comment was minimized by the moderator on the site
I must be doing something incorrect. When I try to use the SumNumbers() VB code nothing happens? excel does not recognize it as code? I am using a macro enabled document. Image attached.
https://ibb.co/6vRtD6N

[img]https://ibb.co/6vRtD6N[/img]
This comment was minimized by the moderator on the site
Hi, Michael,
I have tested the code based on the data you provied, it can work well.
This is User Defined Function, so, you needn't run the code, you just need to insert the formula SumNumbers(A1), and press Enter key to get the result. See screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-sum-text-number.png

Please try, hope it can help you!
This comment was minimized by the moderator on the site
How to do this, but with decimal places? When using decimals, it returns 0...
This comment was minimized by the moderator on the site
HOW TO MULTIPLY COLUM 1 , 2 PCS , COLOUM 2 RATE 1 USD TOTAL AMOUNT ?
This comment was minimized by the moderator on the site
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?"
This comment was minimized by the moderator on the site
You need to use the VBA for that to work. the VBA code defines the SumNumbers as a function.
This comment was minimized by the moderator on the site
I have used VBA for that but it doesn't work. Can you plz take a look?
This comment was minimized by the moderator on the site
I have used VBA for that but it does not work. Can you take a look plz?
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations