Check excel column values if they are equal to each other

Question

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)

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).

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).

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.

There are some ideas for counting unique values in a filtered list by formula here blog.contextures.com/archives/2010/10/04/… — Tom Sharpe

Gary's Student Gary's Student · Accepted Answer · 2016-01-30T15:32:47

Say we have an autofiltered table like:

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

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:

=SUBTOTAL(109,B2:B6)

with:

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

Check excel column values if they are equal to each other

1 Answers