跳到主要內容

如何在Excel中求和相鄰單元格是否相等，為空白或包含文本？

使用Microsoft Excel時，您可能需要對相鄰單元格等於某個範圍內的條件的值求和，或者對相鄰單元格為空白或包含文本的值求和。 在本教程中，我們將為您提供解決這些問題的公式。

Sumif相鄰單元格等於Excel中的條件
Sumif相鄰單元格在Excel中為空
在Excel中將包含文本的相鄰單元格求和

Sumif相鄰單元格等於Excel中的條件

如下圖所示，您需要對牛肉的所有價格求和。 請執行以下操作。

1.指定一個用於顯示結果的單元格。

2.複製並粘貼公式 =SUM(IF(A4:E10=A13, B4:F10, 0)) 到 配方欄，然後按 按Ctrl + 轉移 同時按Enter鍵可獲得結果。

備註：在公式中，A4：E10是包含所需特定條件的數據范圍，A13是您要基於其對像元求和的條件，而B4：F10是具有要求和的值的範圍。 。 請根據您自己的數據更改範圍。

輕鬆合併一列中的重複項，並基於Excel中的重複項將另一列中的值求和

Excel的Kutools's 高級合併行 實用程序可幫助您輕鬆地合併列中的重複行，並基於Excel中的重複項來計算或合併另一列中的值
立即下載並試用！ （30 天免費試用）

Sumif相鄰單元格在Excel中為空

例如，您有一個範圍A2：B7，只需要對相鄰單元格為空的值求和。 請參見下面的屏幕截圖。 您需要執行以下操作。

1.選擇一個空白單元格以顯示結果。 複製並粘貼公式 = SUM（IF（ISBLANK（B2：B7），A2：A7,0）） （B2：B7是包含空白單元格的數據范圍，而A2：A7是要求和的數據）進入公式欄，然後按 按Ctrl + 轉移 + Enter 同時獲得結果。

然後，您可以看到所有相鄰單元格為空白的值相加並顯示在指定的單元格中。

在Excel中將包含文本的相鄰單元格求和

上面的方法可以幫助您求和一定範圍內相鄰單元格為空白的值。 在本節中，您將學習如何在Excel中相鄰單元格包含文本的地方對值求和。

只需使用與上述方法相同的示例即可。

1.選擇一個空白單元格，複製並粘貼公式 = SUMIF（B2：B7，“ <>”＆“”，A2：A7） （B2：B7是包含文本單元格的數據范圍，而A2：A7是要求和的數據）進入公式欄，然後按 按Ctrl + 轉移 + Enter 鍵。

您將看到在相鄰單元格中包含文本的所有值被求和並顯示在所選單元格中。

最佳辦公生產力工具

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

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

產品描述

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

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (14)
No ratings yet. Be the first to rate!
Cole
Cole
This comment was minimized by the moderator on the site
Sumif Adjacent Cell Equals A Criterion In Excel formula is not working for me, it is adding everything instead of just the cells with data we want.
Reply
0
0
crystal
crystal     Cole
This comment was minimized by the moderator on the site
Hi Cole,
Which Excel version are you using? Would you mind providing a screenshot of your data?
Reply
Report
0
0
erin
erin
This comment was minimized by the moderator on the site
Hello I am trying to set up a pricing list. I have used a dropdown list for the selected products. How do I make excel input a price based on the choice of the text in the dropdown list.
Reply
0
0
crystal
crystal     erin
This comment was minimized by the moderator on the site
Hi erin,
Methods in this article can help you. Please have a try: https://www.extendoffice.com/documents/excel/2400-excel-drop-down-list-auto-populate.html
Reply
Report
0
0
wthomas
wthomas
This comment was minimized by the moderator on the site
I am trying to get a return value of "yes" if A1:A7 contains any text. What formulas can I use?
Reply
Report
0
0
crystal
crystal     wthomas
This comment was minimized by the moderator on the site
Dear Wyman Thomas,
Supposing you need to return value of "Yes" if cell A1 contains "Apple", this formula can help you =IF(ISNUMBER(SEARCH("Apple",A1)), "Yes", "No").
Reply
Report
0
0
V
V
This comment was minimized by the moderator on the site
Pretty sure

=SUMIF(B2:B7,"<>"&"",A2:B7) should be


=SUMIF(B2:B7,"<>"&"",A2:A7)
Reply
0
0
Md Islam
Md Islam     V
This comment was minimized by the moderator on the site
Few People Notice that, We take it A2:A7
Reply
0
0
crystal
crystal     V
This comment was minimized by the moderator on the site
Hi,
Thank you for your reminding.
Reply
Report
0
0
Jorge Barbi Martins
Jorge Barbi Martins
This comment was minimized by the moderator on the site
Dear all. I would like to use the SUMIF function in the following way: -Sum range is 1:1 -Criteria Range is 2:2 -Criteria is "jorge" However, I would like the function o work a little bit different. If "jorge" is found on B2, I would like A1 to be sum instead of A2. I tried =SUMIF(1:1,OFFSET(2:2,,1),"jorge") but excel does not accept such criteria range argument. How to get it working?
Reply
0
0
Mike
Mike
This comment was minimized by the moderator on the site
I am looking to create a budget spreadsheet. My goal is to plug in my daily expenses, with the category in one cell and the amount in the cell beneath. As far as my spending goes, I have no problem calculating. However, I am struggling with a formula to create a running tally of each category. Can you help?
Reply
0
0
Joshua
Joshua     Mike
This comment was minimized by the moderator on the site
Mike I am working on the same problem I created a drop down list for column D and my costs are in column C. Simply change the name (Contracts) of the formula to your category name. =SUMIF(D2:D31,""Contracts"",C2:C31)
Reply
0
0
jayson
jayson
This comment was minimized by the moderator on the site
hi, hope you could help me on this i want to create a formula where-in the codes under the subnet, be reflected on the net. see below place(net) 1 place1 2 place2 3 place3 4 place4 5 place5 where place net should have a code 1+2+3+4+5, i know how to concatenate and use "&" but i need to have a formula where i could drag it down since its not the only net in my specs, hoping for your answer? bog thanks regards, jayson
Reply
0
0
Wyman
Wyman     jayson
This comment was minimized by the moderator on the site
I would like to get the return value "yes" if the cells in A1:A7 contain data. What formula can I use?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations