根据其他列中的条件对列中的唯一值求和(如果对列进行了过滤) [英] Sum up unique values in column per criteria in other column (if columns are filtered)

问题描述

参考

如您所见，单元格B1 中的 1.900 仍然存在.但是，我只想将 Cell B1 中的公式应用于过滤后的数据，这意味着-在这种情况下- Cell B1 中的值应为 1.000 .

---

通常，我可以使用 SUBTOTAL 公式实现此目的，但是我不知道如何组合 UNIQUE 和 INDEX 函数和 SUBTOTAL .

解决方案

说实话:

  = SUM(INDEX(UNIQUE(IF(SUBTOTAL(2，OFFSET(A2:A9，ROW(A2:A9)-ROW(A2)，1,1))，A2:B9)),,2)) 

您总是可以避免使用volatile函数并使用:

  = SUM(INDEX(UNIQUE(FILTER(A2:B9，(A2:A9"Product_A")**(A2:A9"Product_C")))，0,2)) 

With reference to this quesion in Cell B1 I use the formula =SUM(INDEX(UNIQUE(A2:B9),,2)) in order to only sumup the unique values per product.

All this works perfectly.

---

Now, I set a filter in Column A and exclude for example Product_A and Product_C from the list:

As you can see the 1.900 in Cell B1 remains. However, I only want to apply the formula in Cell B1 to the filtered data which means that - in this case - the value in Cell B1 should be 1.000.

---

Normally, I could achieve this with a SUBTOTAL formula but I do not have any clue how I can combine the UNIQUE and INDEX function with the SUBTOTAL.

解决方案

Bit of a stretch to be honest:

=SUM(INDEX(UNIQUE(IF(SUBTOTAL(2,OFFSET(A2:A9,ROW(A2:A9)-ROW(A2),1,1)),A2:B9)),,2))

You could always just avoid the volatile function and use:

=SUM(INDEX(UNIQUE(FILTER(A2:B9,(A2:A9<>"Product_A")*(A2:A9<>"Product_C"))),0,2))

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