跳到主要内容

如何基于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 键一起获得第一个总结果，然后选择此公式单元格并将填充手柄向下拖动到单元格以获取每个项目的所有总值。 看截图：

Tips：如果您需要基于两个条件对所有唯一值求和，请使用以下公式：

=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和sum函数可帮助您快速找出指定的条件并同时求和相应的值。 在本文中，我们将向您展示两种方法来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