Hoppa till huvudinnehåll

Hur summerar jag celler med text och siffror i Excel?

Till exempel har jag en lista med värden som innehåller numeriska och textsträngar, nu vill jag bara summera siffrorna baserat på samma text, titta på följande skärmdump. Normalt kan du inte summera värdena i listan med textsträng direkt, här presenterar jag några formler för att hantera denna uppgift.


Sumceller som innehåller text och siffror baserade på viss text med formel

Du kan använda en matrisformel för att summera siffrorna baserat på deras motsvarande textsträng i cellen, gör så här:

1. Först kan du skriva ner dina textsträngar som du vill summera de relativa siffrorna i en kolumnceller.

2. Skriv sedan formeln nedan i en tom cell, i det här exemplet anger jag formeln i cell E2 och trycker sedan på Ctrl + Shift + Enter knapparna tillsammans, kommer det att returnera det totala värdet från listan baserat på viss text. Se skärmdump:

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

Anmärkningar: I ovanstående formel, D2 är det kriterium som du vill använda, och B2: B9 anger det cellintervall du vill summera.

3. Välj sedan formelcellen och dra fyllningshandtaget ner till cellerna du vill använda denna formel för att få resultatet:


Summa celler som innehåller text och siffror baserat på viss text med en praktisk funktion

Om formeln ovan är svår att förstå, Kutools för Excel ger ett användbart verktyg - Summa baserat på samma text, med den här funktionen kan du få det totala resultatet i en cell som blandas med siffror och text utan att komma ihåg några formler.

Notera:För att tillämpa dessa Summa baserat på samma text funktioner, först bör du ladda ner Kutools för Excel, och använd sedan funktionerna snabbt och enkelt.

När du har installerat Kutools för Excel, gör så här:

1. Klicka för att välja en cell där du vill mata ut resultatet, se skärmdump:

tips: För att förhindra cirkulära referenser, vänligen sätt inte in denna formel i den första raden i kalkylbladet.

2. Och klicka sedan på Kutools > Formel Helper > Formel Helper, se skärmdump:

3. I Formlerhjälpare dialogrutan, gör följande:

  • Välja Statistisk från Formel typ rullgardinsmeny;
  • I Välj en formel listruta, klicka för att välja Summa baserat på samma text alternativ;
  • Då, i Argumentinmatning markera det cellområde som innehåller texten och siffrorna som du vill summera i Mätområde textruta och välj sedan den textcell som du vill summera värden baserat på text textruta.
tips: I text textruta, bör du ändra den absoluta absoluta cellreferensen till den relativa cellreferensen för att dra formeln korrekt.

4. Och klicka sedan på Ok -knappen kommer det första resultatet att beräknas, dra sedan fyllningshandtaget för att fylla formeln till andra celler, se skärmdump:


Sumceller som innehåller text och siffror som ignorerar texten med användardefinierad funktion

Om du har följande data som blandas med text och siffror och du bara vill extrahera och bara lägga till siffrorna i varje cell, hur kan du snabbt avsluta detta i Excel ?.

För att hantera detta problem kan du skapa en Användardefinierad funktion, gör så här:

1. Håll ner ALT + F11 nycklar för att öppna Microsoft Visual Basic for Applications-fönstret.

2. Klicka Insert > Modulernaoch klistra in följande kod i Modulfönster.

VBA-kod: summerar endast siffrorna i cellen som innehåller siffror och text

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. Spara och stäng sedan den här koden, gå tillbaka till kalkylbladet och ange den här formeln = SumNumbers (A2) (A2 anger cellen som du vill summera siffrorna), dra sedan påfyllningshandtaget ner till cellerna du vill fylla formeln, och endast siffror i varje cell läggs samman, se skärmdump:


Fler relativa artiklar:

  • Kombinera duplicerade rader och summera värdena i Excel
  • I Excel , kan du alltid möta detta problem när du har en rad data som innehåller några dubblettposter och nu vill du kombinera duplikatdata och summera motsvarande värden i en annan kolumn, som följande skärmdumpar visas. Hur kunde du lösa detta problem?
  • Multiplicera två kolumner och summera sedan i Excel
  • I Excel kanske de flesta av oss lider av att multiplicera två kolumner och sedan lägga till dem, naturligtvis, vi kan multiplicera varje objekt och sedan summera dem, men det kommer att vara besvärligt om det finns hundratals eller tusentals rader som behöver beräknas. I Excel finns det en kraftfull funktion - SUMPRODUCT, med den kan vi snabbt multiplicera två kolumner och sedan summera dem. Den här artikeln kommer jag att prata om hur man använder denna funktion.
  • Summa 3 eller N värden baserade på kriterier i Excel
  • Normalt kan vi summera de översta n-värdena från ett cellområde med hjälp av en enkel formel, men har du någonsin försökt att summera topp-n-värden baserat på vissa kriterier. Den här artikeln kommer jag att introducera några formler för att summera topp n-värden med specifika kriterier i Excel.
  • Summa unika värden baserade på kriterier i Excel
  • Till exempel har jag en rad data som innehåller kolumnerna Namn och ordning, nu för att bara summera unika värden i kolumnen Order baserat på kolumnen Namn enligt följande skärmdump. Hur löser jag den här uppgiften snabbt och enkelt i Excel?
  • Räkna eller summera celler baserat på teckensnittsfärgerna i Excel
  • Hur kan du få antalet celler eller summera alla celler som innehåller en viss teckensnittsfärg i Excel? Till exempel har jag en rad data i ett kalkylblad enligt följande skärmdump, och nu vill jag respektive räkna eller summera cellerna som har en röd, blå och svart teckensnittsfärg. Normalt finns det inget direkt sätt att hantera denna uppgift, här kommer jag att prata om några knep för att lösa detta jobb.

Bästa kontorsproduktivitetsverktyg

Populära funktioner: Hitta, markera eller identifiera dubbletter   |  Ta bort tomma rader   |  Kombinera kolumner eller celler utan att förlora data   |   Rund utan formel ...
Superuppslag: Flera kriterier VLookup    VLookup med flera värden  |   VSök över flera ark   |   Fuzzy Lookup ....
Avancerad rullgardinslista: Skapa snabbt en rullgardinslista   |  Beroende rullgardinslista   |  Flervals-rullgardinslista ....
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  |  Jämför intervall och kolumner ...
Utvalda funktioner: Rutnätsfokus   |  Designvy   |   Stor formelbar    Arbetsbok & Bladhanterare   |  Resursbibliotek (Automatisk text)   |  Datumväljare   |  Kombinera arbetsblad   |  Kryptera/Dekryptera celler    Skicka e-postmeddelanden efter lista   |  Superfilter   |   Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser12 text verktyg (lägga till text, Ta bort tecken, ...)   |   50 + Diagram Typer (Gantt Chart, ...)   |   40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag, ...)   |   19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg, ...)   |   12 Konvertering verktyg (Siffror till ord, Valutaväxling, ...)   |   7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela celler, ...)   |   ... och mer

Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid.  Klicka här för att få den funktion du behöver mest...

kte-flik 201905


Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!
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