根据其他列中的条件对列中的唯一值求和 [英] Sum up unique values in column per criteria in other column

问题描述

  |甲乙--- | -----------------------------------------1 |总计1.900--- | -----------------------------------------2 |产品_A 7003 |产品_A 700--- | -----------------------------------------4 |产品_B 300--- | -----------------------------------------5 |产品_C 2006 |产品_C 200--- | ------------------------------------------7 |产品_D 7008 |产品_D 7009 |产品D_70010 | 

---

在单元格 B1 中，我想对 B列中的唯一值求和中的每个产品列A .

  B1 = 700 + 300 + 200 + 700 

当前，在单元格 B1 中，我使用

   |       A                 B           
---|----------------------------------------- 
1  |    Total              1.900
---|-----------------------------------------
2  |    Product_A           700        
3  |    Product_A           700  
---|-----------------------------------------      
4  |    Product_B           300        
---|-----------------------------------------
5  |    Product_C           200      
6  |    Product_C           200      
---|------------------------------------------
7  |    Product_D           700         
8  |    Product_D           700      
9  |    Prodcut_D           700  
10 |

---

In Cell B1 I want to sum the unique values in Column B per product in Column A.

B1 = 700 + 300 + 200 + 700

Currently, in Cell B1 I use the formular from this question which is =SUM(UNIQUE(B2:B9)).
However, with this formula I get 1.200 as result because it does not include the 700 from Product_D.

---

How do I have to modify the formula so it sums up the values unique per product?

解决方案

As you have UNIQUE() function then you can use it with index and sum like-

=SUM(INDEX(UNIQUE(A2:B9),,2))

这篇关于根据其他列中的条件对列中的唯一值求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！