如果excel列值彼此相等，请检查它们

I have a table like this

我有一张这样的桌子

Product       Price     Currency
________   _____   ________
Product 1      10      USD
Product 2      11      EUR
Product 3      12      USD
Product 4      13      CNY
Product 5      14      EUR
                  _____
            =subtotal(109)

产品价格货币________ _____ ________产品1 10美元的产品2 11欧元产品3 12美元的产品4 13元5 14欧元_____ =小计(109)

I use filter in Currency column, and subtotal(109) formula in price column to sum prices. So if USD is selected from filter, it filters all products with USD price and sums them. But if currency is not filtered subtotal(109) sums prices anyway, which is wrong (USD 1 + EUR 1 is not 2).

我在货币列中使用过滤器，在price列中使用subtotal(109)公式来求和价格。因此，如果从过滤器中选择USD，它会过滤所有的产品，并对其进行汇总。但是如果货币不是经过过滤的小计(109)和价格，那就错了(1美元+ 1欧元不是2美元)。

I want to fire subtotal(109) formula only if values in currency column are equal to each other. Like, count NOT unique values among filtered rows, and if that is equal to 1 fire subtotal(109).

只有当货币列中的值相等时，我才使用subtotal(109)公式。例如，在经过过滤的行中不计算唯一值，如果它等于一个fire subtotal(109)。

Note: I know how to do it with SUMIF by placing a dropdown list with currencies in a separate cell. But i want to use filter to do this task.

注意:我知道如何使用SUMIF，将带有货币的下拉列表放在一个单独的单元中。但是我想使用filter来完成这个任务。

1 个解决方案

#1

Say we have an autofiltered table like:

假设我们有一个自动过滤的表:

but we want a warning rather than a SUBTOTAL() if more than one currency has been selected.

但是，如果选择了不止一种货币，我们需要一个警告而不是一个SUBTOTAL()。

We need to detect more than one currency.

我们需要发现不止一种货币。

First enter the following User Defined Function in a standard module:

首先在标准模块中输入以下用户定义函数:

Option Explicit

Public Function CountVisibleUnique(rng As Range) As Long
   Dim c As Collection, r As Range
   Set c = New Collection

   On Error Resume Next
      For Each r In rng
         If r.EntireRow.Hidden = False Then
            c.Add r.Text, CStr(r.Text)
         End If
      Next r
   On Error GoTo 0

   CountVisibleUnique = c.Count
End Function

Then in cell B8, replace:

然后在单元格B8中，替换:

=SUBTOTAL(109,B2:B6)

with:

=IF(countvisibleunique(C2:C6)>1,"multiple currencies",SUBTOTAL(109,B2:B6))

#1