跳到主要內容

如何基於Excel中的條件求和唯一值？

例如，我現在有一系列數據，其中包含“名稱”和“訂單”列，以便根據“名稱”列僅對“訂單”列中的唯一值求和，如下圖所示。 如何在Excel中快速輕鬆地解決此任務？

基於一個或多個條件與數組公式求和的唯一值

基於一個或多個條件與數組公式求和的唯一值

要僅基於另一列中的條件求和唯一值，請使用以下公式：

1。 請複制以下數組公式或將其輸入到空白單元格中：

=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12))

備註：在以上公式中， A2：A12 是列數據包含條件值， D2 是您要基於以下條件求和的條件， B2：B12 是包含您要求和的值的列。

2。 然後按 Ctrl + Shift + Enter 鍵一起獲得第一個總結果，然後選擇此公式單元格並將填充手柄向下拖動到單元格以獲取每個項目的所有總值。 看截圖：

保養竅門：如果您需要基於兩個條件對所有唯一值求和，請使用以下公式：

=SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))

更多相關文章：

  • 在Excel中基於單個條件求和多列
  • 在Excel中，您可能始終需要根據一個條件對多列求和。 例如，我有一系列數據，如下面的屏幕快照所示，現在，我想獲取三個月（一月，二月和三月）的KTE總值。
  • 在Excel中的行或列中的Vlookup和總和匹配
  • 使用vlookup和求和功能可以幫助您快速找出指定的條件並同時求和相應的值。 在本文中，我們將向您展示兩種方法來vlookup並求和Excel中行或列中第一個或所有匹配的值。
  • 在Excel中基於月和年的總和值
  • 如果您有一系列數據，則A列包含一些日期，B列包含訂單數，現在，您需要根據另一列中的月份和年份對數字求和。 在這種情況下，我想計算2016年XNUMX月的總訂單數，以得到以下結果。 在本文中，我將討論一些解決Excel中這項工作的技巧。
  • Excel中基於文本條件的總和值
  • 在Excel中，您是否曾經嘗試過根據另一列文本條件求和？ 例如，我在工作表中有一系列數據，如下面的屏幕快照所示，現在，我想將B列中與滿足特定條件的A列中的文本值相對應的所有數字加起來，例如，如果列A中的單元格包含KTE。
  • 基於Excel中下拉列表選擇的總和值
  • 如下面的屏幕快照所示，您有一個包含Category列和Amount列的表，並且創建了一個包含所有類別的數據驗證下拉列表。 當從下拉列表中選擇任何種類的類別時，您要對B列中所有對應的單元格值求和，並將結果填充到指定的單元格中。 例如，當從下拉列表中選擇類別CC時，您需要對單元格B5和B8中的值求和，並獲得總數40 + 70 = 110。 如何實現呢？ 本文中的方法可以為您提供幫助。
  • 超級公式欄 （輕鬆編輯多行文本和公式）； 閱讀版式 （輕鬆讀取和編輯大量單元格）； 粘貼到過濾範圍...
  • 合併單元格/行/列 和保存數據； 拆分單元格內容； 合併重複的行和總和/平均值...防止細胞重複； 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 （所有單元格都是空的）； 超級查找和模糊查找 在許多工作簿中； 隨機選擇...
  • 確切的副本 多個單元格，無需更改公式參考； 自動創建參考 到多張紙； 插入項目符號，複選框等...
  • 收藏并快速插入公式，範圍，圖表和圖片； 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 提取文字，添加文本，按位置刪除， 刪除空間; 創建和打印分頁小計； 在單元格內容和註釋之間轉換...
  • 超級濾鏡 （將過濾方案保存並應用於其他工作表）； 高級排序 按月/週/日，頻率及更多； 特殊過濾器 用粗體，斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表； 將數據分割成多個工作表; 批量轉換xls，xlsx和PDF...
  • 數據透視表分組依據 週號，週幾等 顯示未鎖定的單元格 用不同的顏色 突出顯示具有公式/名稱的單元格...
kte選項卡201905
  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
officetab底部
Comments (9)
No ratings yet. Be the first to rate!
Pugazh
Pugazh
This comment was minimized by the moderator on the site
I have column "A" listed with various Part nos. Say 50 parts. Each of these 50 parts will be repeated more than once. I column B where in for each of these part nos stock qty say for particular part no 1000 is the qty. This 1000 Qty is shown againt that particular part no in whichever cell it comes in column 'A".

The problem i am facing is sum total of column B is not correct as stock qty is shown n-number of times that particular part finds place in column A.

How do i avoid totaling up this repeated stock qty of one unique part. Say part no P1001 stock is 1000nos. If delivery date of this part P1001 is

Part no..Date.............Qty......Stock
P1001....05-10-22 .....150......1000
P1001....06-10-22......200.....1000
P1001....07-10-22.......250.....1000

in the above table stock is only 1000 but it shows against each delivery date the same stock qty. But when i want to sum the total stock of all part nos it should not take 1000 3 times but only 1 time. How do this?
Reply
0
0
skyyang
skyyang     Pugazh
This comment was minimized by the moderator on the site
Hello, Pugazh
Did you want to sum only the unique stock based on the part number?
Or you can give your problem as a creenshot here, so that, we can understand more clearly.
Thank you!
Reply
Report
0
0
Gus
Gus
This comment was minimized by the moderator on the site
Hi,

I was wondering if there was a formula for three criteria? Or if there's a process/logic to follow for more than two criteria ?

Many thanks,
Gus
Reply
0
0
skyyang
skyyang     Gus
This comment was minimized by the moderator on the site
Hello, Gus,
If there are three criteria of your data, to sum the unique values, please apply the below array formula:
=SUM(IF(FREQUENCY(IF($A$2:$A$10=F2,IF($B$2:$B$10=G2,IF($C$2:$C$10=H2,MATCH($D$2:$D$10,$D$2:$D$10,0)))),ROW($D$2:$D$10)-ROW($C$2)+1),$D$2:$D$10))

After inserting the formula, please press press Ctrl + Shift + Enter keys together to get the result. see below screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-sum-unique.png
Please have a try, thank you!
Reply
Report
0
0
Louise
Louise
This comment was minimized by the moderator on the site
Hi,
I would like to sum based on two unique criteria, is this possible?
In the second example, rather than James and October being the specific criteria, James would stay the specific element, and the formula would sum based on the name and the month being unique.
Many thanks
Louise
Reply
0
0
jorge molina
jorge molina
This comment was minimized by the moderator on the site
Hola me podrias ayudar deseo sumar o contar solo valores unicos de fechas especificas me puedes ayudar ya que la formula =SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12)) me sirve pero como esta en ingles no se como colocarla, te agradezco mucho
Reply
0
0
MandyZhou
MandyZhou     jorge molina
This comment was minimized by the moderator on the site
Hello Jorge,

Formula: =SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))

Translate the English version formula into the Spanish version:
=SUMA(SI(FRECUENCIA(SI($A$2:$A$12=E2,SI($B$2:$B$12=F2,COINCIDIR($C$2:$C$12,$C$2:$C$12,0))),FILA($C$2:$C$12)-FILA($C$2)+1),$C$2:$C$12))

Please have a try.

Sincerely,
Mandy
Reply
Report
0
0
kc
kc
This comment was minimized by the moderator on the site
This doesn't actually work as I would have expected. The formula is only counting the unique criteria.

James had 700 units ordered in the first example, yet the total says 600.

The formula won't count his orders that contain the same qty because they are unique. Is there a way to modify this formula so you can get an actual total of all of James' orders?

The second formula has the same issue. James ordered 400 units, not 300 as shown in the summary. 
Reply
0
0
skyyang
skyyang     kc
This comment was minimized by the moderator on the site
Hello, kc, May be the below article can solve your problem, please try:https://www.extendoffice.com/documents/excel/2471-excel-sumif-multiple-criteria.html
Reply
Report
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