跳到主要內容

如何在一列中將多個條件相加?

在Excel中,SUMIF函數是有用的函數,可用於匯總不同列中具有多個條件的單元格,但是使用此功能,我們還可以基於一列中的多個條件對單元格求和。 在這篇文章中。 我將討論如何在同一列中使用多個條件對值求和。

將一列中包含多個OR條件的單元格與公式相加


箭頭藍色右氣泡 將一列中包含多個OR條件的單元格與公式相加

例如,我有以下數據范圍,現在,我想獲得一月份產品KTE和KTO的總訂單。

doc-sum-multiple-criteria-column-1

對於同一字段中的多個OR條件,請使用多個SUMIF函數:

公式1:SUMIF + SUMIF

請輸入以下公式: =SUMIF(A2:A10,"KTE",B2:B10) + SUMIF(A2:A10,"KTO",B2:B10),然後按 Enter 密鑰,您將獲得產品KTE和KTO的總價值,請參見屏幕截圖:

doc-sum-multiple-criteria-column-2
-1
doc-sum-multiple-criteria-column-3

筆記:

1.在上式中 A2:A10 是您要對其應用條件的單元格範圍, B2:B10 是要求和的單元格,而KTE,KTO是要對這些單元格求和的條件。

2.在此示例中,只有兩個條件,您可以應用更多條件,只需在公式後添加SUMIF(),例如 = sumif(範圍,條件,sum_range)+ sumif(範圍,條件,sum_range)+ sumif(範圍,條件,sum_range)+…

公式2:SUM和SUMIF

如果需要添加多個條件,那麼上面的公式將是冗長而乏味的,在這種情況下,我可以給您一個更緊湊的公式來解決它。

將此公式鍵入一個空白單元格: = SUM(SUMIF(A2:A10,{“ KTE”,“ KTO”},B2:B10)),然後按 Enter 獲得所需結果的關鍵,請參見屏幕截圖:

doc-sum-multiple-criteria-column-4

筆記:

1.在上式中 A2:A10 是您要對其應用條件的單元格範圍, B2:B10 是您要求和的單元格,並且 KTE, 韓國旅遊發展局 是您對單元格求和的標準。

2.要匯總更多條件,只需將條件添加到花括號中,例如 = SUM(SUMIF(A2:A10,{“ KTE”,“ KTO”,“ KTW”,“ Office Tab”},B2:B10)).

3.僅當要在同一列中應用條件的範圍單元格時,才能使用此公式。


高級合併行:(合併重複的行並求和/平均對應的值):
  • 1.指定要基於其合併其他列的鍵列;
  • 2.為您的合併數據選擇一種計算。

doc-sum-columnsone-criteria-7

Excel的Kutools:具有300多個方便的Excel加載項,可以在30天內免費試用,沒有任何限制。 立即下載並免費試用!


相關文章:

如何在Excel中對一個或多個條件求和?

如何在Excel中基於單個條件求和多列?

最佳辦公生產力工具

🤖 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 (20)
Rated 4.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Queria somar em um intervalo, onde o critério está em uma coluna, mas ao chegar no critério a soma parasse.

A B
1 Amor 3
2 Paixão 4
3 Ódio 6
4 Raiva 1
5 Excel 2
6 Carro 9
7 Moto 6
8 Avião 5

Somar de Paixão até moto, mas não é fixa a quantidade linhas. Então a função teria que começar a somar em Paixão e parar a soma em Moto.

4+6+1+2+9+6 = 28
This comment was minimized by the moderator on the site
Busquei muito no Gogle uma forma de somar valores diferentes dentro da mesma coluna e com sua dica, consegui o que eu queria! Valeu!!!
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
what if instead of "KTE" and "KTO" I wanna use E:1 and E:2 cells, please help

=SUM(SUMIF(A2:A10, {"KTE","KTO"}, B2:B10))
This comment was minimized by the moderator on the site
Hello, Alejandro,
To use the cell references instead of the specific text value, you just need to apply the below array formula:
=SUM(SUMIF(A2:A10,E1:E2,B2:B10))
After entering this formula, please press Ctrl + Shift + Enter keys together to get the result.
This comment was minimized by the moderator on the site
O meu problema é, em um intervalo de 5000 linhas, tenho que somar 630, mas o titulo fica na mesma coluna dos critérios
This comment was minimized by the moderator on the site
thanks this helped a lot! :)
This comment was minimized by the moderator on the site
Hi, I Need to put in a drop Down in 3 Slab for using sumif. Example Slab 1-A,2-B,3-C & Total Sumof 3 Slab Please Suggest..
This comment was minimized by the moderator on the site
Hi, I Need to Understand, How to Make the multiple criteria in sumifs function along with Total Value in a Drop Down. Please suggest. Example In a Drop Down I need to put 4 Slab (1-A,1-B,1-C & Total of (1-A,1-B,1-C) Slab. JItendra//
This comment was minimized by the moderator on the site
As shown above, we can do either =SUMIF(A2:A10,"KTE",B2:B10) + SUMIF(A2:A10,"KTO",B2:B10) or =SUM(SUMIF(A2:A10, {"KTE","KTO"}, B2:B10)) for short What if I don't want to use KTE and KTO directly in the code and use the cell they are into? How am I suppose to write the code for the short version?
This comment was minimized by the moderator on the site
=SUM(SUMIF(A2:A10, {A:5\A:8}, B2:B10))
This comment was minimized by the moderator on the site
This is what I am looking for. Help me if you got answer of this
This comment was minimized by the moderator on the site
Hi, Is there a way to use INDIRECT function, eg INDIRECT(E3), instead of using "Apple" in the SUMIF function? eg. SUM(SUMIF(A1:A5, {"Apple","Orange"}, C1:C5)) and use something like this SUM(SUMIF(A1:A5,(INDIRECT(E3),INDIRECT(E4)),C1:C5)?
This comment was minimized by the moderator on the site
I'm hoping someone can help me with this. Name Categories John 1, 5, 8, 10, 12 Mike 4, 8, 9, 11, 15 Brittany 2, 5, 14, 23 Angela 1, 6, 7, 14, 19 David 11, 10, 23 In the above scenario, the categories for each person are what would be within the brackets of a SUM(SUMIFS( formula. I am trying to create a formula that can be dragged down for my entire data set. The problem is my data set is for hundreds of people, so creating IF statements for each scenario makes the formula entirely too long. Is there any way the criteria within the brackets can be a cell reference? Or if there are any other suggestions I would greatly appreciate it. Thanks!
This comment was minimized by the moderator on the site
Is there a way to do an "AND" statement instead of an "OR" statement?
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