Alter Formula for unique value to sum only visible cells

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,516
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

I am using this formula to count unique values in a range


Code: 
=SUM(IF(FREQUENCY(IF(LEN(orders_ref)>0,MATCH(orders_ref,orders_ref,0),""),IF(LEN(orders_ref)>0,MATCH(orders_ref,orders_ref,0),""))>0,1))

I want this formula to count only visible cells....

If anyone can provide a solution... Like alter or change it a bit.


Regards,

Humayun
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(orders_ref,ROW(orders_ref)-ROW(INDEX(orders_ref,1,1)),0)),IF(orders_ref<>"",MATCH(orders_ref,orders_ref,0))),ROW(orders_ref)-ROW(INDEX(orders_ref,1,1))+1),1))
 
Upvote 0
Hi Aladin,

Thanks for the reply....

Formula you provide is not giving correct results... I would like to mention here that the list is not a regular autofilter.

I am using the below code to filter records..

Code: 
Private Sub Worksheet_Change(ByVal Target As Range) 
Dim lngRow              As Long
    Dim blnA                As Boolean
    Dim blnB                As Boolean
    Dim blnC                As Boolean
    Dim blnD                As Boolean
    Dim blnE                As Boolean
    Dim blnF                As Boolean
    Dim blnG                As Boolean
    Dim blnH                As Boolean
    Dim blnI                As Boolean
    Dim blnJ                As Boolean
    Dim blnK                As Boolean
    Dim blnL                As Boolean
    Dim blnM                As Boolean
    Dim blnN                As Boolean
    Dim blnO                As Boolean
    Dim blnP                As Boolean
    Dim blnQ                As Boolean
    Dim blnR                As Boolean
    Dim blnS                As Boolean
    Dim blnT                As Boolean
    Dim blnU                As Boolean
    Dim blnV                As Boolean
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    With ActiveSheet
        
        For lngRow = 4 To 1002
            If lngRow <= 1002 Then
                blnA = InStr(UCase(.Range("A" & lngRow).Value), UCase(.Range("A2").Value)) Or (Len(.Range("A2").Value) = 0)
                blnB = InStr(UCase(.Range("B" & lngRow).Value), UCase(.Range("B2").Value)) Or (Len(.Range("B2").Value) = 0)
                blnC = InStr(UCase(.Range("C" & lngRow).Value), UCase(.Range("C2").Value)) Or (Len(.Range("C2").Value) = 0)
                blnD = InStr(UCase(.Range("D" & lngRow).Value), UCase(.Range("D2").Value)) Or (Len(.Range("D2").Value) = 0)
                blnE = InStr(UCase(.Range("E" & lngRow).Value), UCase(.Range("E2").Value)) Or (Len(.Range("E2").Value) = 0)
                blnF = InStr(UCase(.Range("F" & lngRow).Value), UCase(.Range("F2").Value)) Or (Len(.Range("F2").Value) = 0)
                blnG = InStr(UCase(.Range("G" & lngRow).Value), UCase(.Range("G2").Value)) Or (Len(.Range("G2").Value) = 0)
                blnH = InStr(UCase(.Range("H" & lngRow).Value), UCase(.Range("H2").Value)) Or (Len(.Range("H2").Value) = 0)
                blnI = InStr(UCase(.Range("I" & lngRow).Value), UCase(.Range("I2").Value)) Or (Len(.Range("I2").Value) = 0)
                blnJ = InStr(UCase(.Range("J" & lngRow).Value), UCase(.Range("J2").Value)) Or (Len(.Range("J2").Value) = 0)
                blnK = InStr(UCase(.Range("K" & lngRow).Value), UCase(.Range("K2").Value)) Or (Len(.Range("K2").Value) = 0)
                blnL = InStr(UCase(.Range("L" & lngRow).Value), UCase(.Range("L2").Value)) Or (Len(.Range("L2").Value) = 0)
                blnM = InStr(UCase(.Range("M" & lngRow).Value), UCase(.Range("M2").Value)) Or (Len(.Range("M2").Value) = 0)
                blnN = InStr(UCase(.Range("N" & lngRow).Value), UCase(.Range("N2").Value)) Or (Len(.Range("N2").Value) = 0)
                blnO = InStr(UCase(.Range("O" & lngRow).Value), UCase(.Range("O2").Value)) Or (Len(.Range("O2").Value) = 0)
                blnP = InStr(UCase(.Range("P" & lngRow).Value), UCase(.Range("P2").Value)) Or (Len(.Range("P2").Value) = 0)
                blnQ = InStr(UCase(.Range("Q" & lngRow).Value), UCase(.Range("Q2").Value)) Or (Len(.Range("Q2").Value) = 0)
                blnR = InStr(UCase(.Range("R" & lngRow).Value), UCase(.Range("R2").Value)) Or (Len(.Range("R2").Value) = 0)
                blnS = InStr(UCase(.Range("S" & lngRow).Value), UCase(.Range("S2").Value)) Or (Len(.Range("S2").Value) = 0)
                blnT = InStr(UCase(.Range("T" & lngRow).Value), UCase(.Range("T2").Value)) Or (Len(.Range("T2").Value) = 0)
                blnU = InStr(UCase(.Range("U" & lngRow).Value), UCase(.Range("U2").Value)) Or (Len(.Range("U2").Value) = 0)
                blnV = InStr(UCase(.Range("V" & lngRow).Value), UCase(.Range("V2").Value)) Or (Len(.Range("V2").Value) = 0)
                .Rows(lngRow).Hidden = ((blnA + blnB + blnC + blnD + blnE + blnF + blnG + blnH + blnI + blnJ + blnK + blnL + blnM + blnN + blnO + blnP + blnQ + blnR + blnS + blnT + blnU + blnV) > -22)
            
            End If
        Next lngRow
        
    End With
      Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
   
End Sub

Any idea ???
 
Upvote 0
Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(orders_ref,ROW(orders_ref)-ROW(INDEX(orders_ref,1,1)),0)),IF(orders_ref<>"",MATCH(orders_ref,orders_ref,0))),ROW(orders_ref)-ROW(INDEX(orders_ref,1,1))+1),1))

Does this work for your "filtering"?
 
Upvote 0
Hi Aladin,

No its not working... But behaving differently with the new formula (103)

Previous formula was doing nothing with filtering. There are 305 unique values as of now. And it was showing a count of 305 whether you filter one record or multiple records.

But this new formula is giving different answers. Like when you filter a single record for example when i filter ref # 123. The formula counts it only 1 (which is right) even if ref # 123 contains more than one row of data. But when i filter records which have more then one ref # then its giving incorrect answers.
 
Last edited:
Upvote 0
The suggested formula should work as intended...

What does the following mean: "But when i filter records which have more then one ref # then its giving incorrect answers." Can you provide a sample with which we can test the formula?
 
Upvote 0
Hi Aladin,

Does the formula you last provided in Post # 4 designed to take care of blank cells ???

There are a lot of blank cells in the range....
 
Upvote 0
Hi Aladin,

Let me explain. Here is a sample data

DATA RANGE > A4:C20
FORMULA >
=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(B4:B20,ROW(B4:B20)-ROW(INDEX(B4:B20,1,1)),0)),IF(B4:B20<>"",MATCH(B4:B20,B4:B20,0))),ROW(B4:B20)-ROW(INDEX(B4:B20,1,1))+1),1))
Click to expand...

Formula showing answer 7 (Which is right coz there are 7 unique values in column B)
[TABLE="width: 260"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P.O #[/TD]
[TD]REF[/TD]
[TD]P.O.DATE[/TD]
[/TR]
[TR]
[TD]95822[/TD]
[TD]123[/TD]
[TD]31-Oct-13[/TD]
[/TR]
[TR]
[TD]95825[/TD]
[TD]123[/TD]
[TD]31-Oct-13[/TD]
[/TR]
[TR]
[TD]95828[/TD]
[TD]123[/TD]
[TD]31-Oct-13[/TD]
[/TR]
[TR]
[TD]95830[/TD]
[TD]123[/TD]
[TD]31-Oct-13[/TD]
[/TR]
[TR]
[TD]95831[/TD]
[TD]123[/TD]
[TD]31-Oct-13[/TD]
[/TR]
[TR]
[TD]40041541[/TD]
[TD]127[/TD]
[TD]27-Sep-13[/TD]
[/TR]
[TR]
[TD]40043227[/TD]
[TD]127[/TD]
[TD]13-Feb-14[/TD]
[/TR]
[TR]
[TD]40042045[/TD]
[TD]129[/TD]
[TD]7-Nov-13[/TD]
[/TR]
[TR]
[TD]12943122 A[/TD]
[TD]130[/TD]
[TD]11-Dec-13[/TD]
[/TR]
[TR]
[TD]12943122 B[/TD]
[TD]130[/TD]
[TD]11-Dec-13[/TD]
[/TR]
[TR]
[TD]672571[/TD]
[TD]131[/TD]
[TD]21-Nov-13[/TD]
[/TR]
[TR]
[TD]78118[/TD]
[TD]132[/TD]
[TD]19-Dec-13[/TD]
[/TR]
[TR]
[TD]78186[/TD]
[TD]132[/TD]
[TD]19-Dec-13[/TD]
[/TR]
[TR]
[TD]78581[/TD]
[TD]132[/TD]
[TD]19-Dec-13[/TD]
[/TR]
[TR]
[TD]76424[/TD]
[TD]133[/TD]
[TD]20-Jan-14[/TD]
[/TR]
[TR]
[TD]76535[/TD]
[TD]133[/TD]
[TD]20-Jan-14[/TD]
[/TR]
[TR]
[TD]76546[/TD]
[TD]133[/TD]
[TD]20-Jan-14[/TD]
[/TR]
</tbody>[/TABLE]




Now applied filter.. Formula still showing answer 7 instead of 1

[TABLE="width: 260"]
<tbody>[TR]
[TD]Filter ------>[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P.O #[/TD]
[TD]REF[/TD]
[TD]P.O.DATE[/TD]
[/TR]
[TR]
[TD]40041541[/TD]
[TD]127[/TD]
[TD]27-Sep-13[/TD]
[/TR]
[TR]
[TD]40043227[/TD]
[TD]127[/TD]
[TD]13-Feb-14[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(102,OFFSET(B4,ROW(B4:B20)-ROW(INDEX(B4:B20,1,1)),0)),IF(B4:B20<>"",MATCH(B4:B20,B4:B20,0))),ROW(B4:B20)-ROW(INDEX(B4:B20,1,1))+1),1))


Use 2 or 103 as function num.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Excel Formula
Replies
1
Views
205
plettieri
P
H
  • Question Question
Add a criteria to VBA Code
Replies
8
Views
365
hrayani
H
H
  • Solved
VBA code required
2
Replies
10
Views
791
Fluff
Fluff
K
  • Question Question
Looking for a function to filter but only want to return back unique visible cells
Replies
1
Views
401
kac1125
K
drom
  • Solved
Count unique values for filtered column
Replies
1
Views
2K
myall_blues
myall_blues

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top