跳到主要內容

如何在Excel中用文字和數字對單元格求和?

例如,我有一個包含數字和文本字符串的值列表,現在,我只想對基於相同文本的數字求和,請看以下屏幕截圖。 通常,您不能直接使用文本字符串將列表中的值求和,在這裡,我將向您介紹一些公式來處理此任務。


包含文本和數字的求和單元格基於帶有公式的某些文本

您可以使用數組公式根據單元格中對應的文本字符串對數字求和,請執行以下操作:

1. 首先,您可以寫下您想要對列單元格中的相對數字求和的文本字符串。

2。 然後在空白單元格中鍵入以下公式,在此示例中,我將在單元格E2中輸入公式,然後按 Ctrl + Shift + Enter 鍵在一起,它將根據特定文本從列表中返回總值。 看截圖:

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

備註:在以上公式中, D2 是您要使用的標準,並且 B2:B9 表示要求和的單元格範圍。

3。 然後選擇公式單元格,然後將填充手柄向下拖動到要應用此公式以獲取結果的單元格:


總和單元格包含基於某些文本的文本和數字,並具有方便的功能

如果上述公式難以理解, Excel的Kutools 提供了有用的工具- 基於相同文本的總和,使用此功能,您可以在一個混合了數字和文本的單元格中獲得總結果,而無需記住任何公式。

注意:應用這些 基於相同文本的總和 功能,首先,您應該下載 Excel的Kutools,然後快速輕鬆地應用這些功能。

安裝後 Excel的Kutools,請執行以下操作:

1。 單擊以選擇要在其中輸出結果的單元格,請參見屏幕截圖:

保養竅門:為防止循環引用,請不要將此公式插入工作表的第一行。

2。 然後點擊 庫工具 > 公式助手 > 公式助手,請參見屏幕截圖:

3。 在 公式助手 對話框,請執行以下操作:

  • 選擇 統計 來自 公式類型 下拉列表;
  • 選擇一個公式 列錶框,單擊以選中 基於相同文本的總和 選項;
  • 那麼,在 參數輸入 部分中,選擇包含您要在其中求和的文本和數字的單元格範圍 範圍 文本框,然後在中選擇要對值求和的文本單元格 文本 文本框。
保養竅門: 在裡面 文本 文本框,應將默認的絕對單元格引用更改為相對單元格引用,以正確拖動公式。

4。 然後,單擊 Ok 按鈕,將計算第一個結果,然後將填充手柄拖動以將公式填充到其他單元格,請參見屏幕截圖:


包含文本和數字的求和單元格使用用戶定義函數忽略文本

如果您具有以下混合了文本和數字的數據,而您只想提取和添加每個單元格中的數字,那麼如何在Excel中快速完成呢?

要解決此問題,您可以創建一個 用戶自定義函數,請這樣做:

1。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications窗口.

2。 點擊 插入 > 模塊,然後將以下代碼粘貼到 模塊窗口.

VBA代碼:僅對包含數字和文本的單元格中的數字求和

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。 然後保存並關閉此代碼,返回到工作表,然後輸入此公式 = SumNumbers(A2) (A2 表示要對數字求和的單元格),然後將填充手柄向下拖動到要填充公式的單元格,並且僅將每個單元格中的數字加在一起,請參見屏幕截圖:


更多相關文章:

  • 合併重複的行並求和Excel中的值
  • 在Excel中,當您具有一系列包含某些重複項的數據時,您可能總是會遇到此問題,現在您想要合併重複數據並在另一列中將相應的值求和,如下面的屏幕截圖所示。 您如何解決這個問題?
  • 將兩列相乘,然後在Excel中求和
  • 在Excel中,我們大多數人可能會遇到兩列相乘然後相加的問題,當然,我們可以將每一項相乘然後相加,但是如果需要計算成百上千的行,這將很麻煩。 在Excel中,有一個強大的功能– SUMPRODUCT,通過它,我們可以快速將兩列相乘然後相加。 在本文中,我將討論如何應用此功能。
  • 根據Excel中的條件求和前3個或N個值
  • 通常,我們可以使用一個簡單的公式將一個單元格區域的前n個值相加,但是您是否曾經嘗試過根據某些條件對前n個值進行相加。 本文中,我將介紹一些公式,用於將Excel中具有特定條件的前n個值相加。
  • 根據Excel中的條件求和唯一值
  • 例如,我現在有一系列數據,其中包含“名稱”和“訂單”列,以便根據“名稱”列僅對“訂單”列中的唯一值求和,如下圖所示。 如何在Excel中快速輕鬆地解決此任務?
  • 基於Excel中字體顏色的單元格計數或求和
  • 如何在Excel中獲取單元格的數量或將包含特定字體顏色的所有單元格求和? 例如,我在工作表中有一系列數據,如下面的屏幕快照所示,現在我想分別對具有紅色,藍色和黑色字體顏色的單元格進行計數或求和。 通常,沒有直接的方法可以處理此任務,在這裡,我將討論解決此任務的一些技巧。

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
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