Alter Formula for unique value to sum only visible cells

[hrayani]

Hello Friends,

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

=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

 

[Aladin Akyurek]

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

 

[hrayani]

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

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 ???

 

[Aladin Akyurek]

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"?

 

[hrayani]

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.

 

[Aladin Akyurek]

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?

 

[hrayani]

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

 

[Aladin Akyurek]

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

Yes, it does.

 

[hrayani]

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

Formula showing answer 7 (Which is right coz there are 7 unique values in column B)

P.O #	REF	P.O.DATE
95822	123	31-Oct-13
95825	123	31-Oct-13
95828	123	31-Oct-13
95830	123	31-Oct-13
95831	123	31-Oct-13
40041541	127	27-Sep-13
40043227	127	13-Feb-14
40042045	129	7-Nov-13
12943122 A	130	11-Dec-13
12943122 B	130	11-Dec-13
672571	131	21-Nov-13
78118	132	19-Dec-13
78186	132	19-Dec-13
78581	132	19-Dec-13
76424	133	20-Jan-14
76535	133	20-Jan-14
76546	133	20-Jan-14

<tbody>
</tbody>

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

Filter ------>	7
P.O #	REF	P.O.DATE
40041541	127	27-Sep-13
40043227	127	13-Feb-14

<tbody>
</tbody>

 

[Aladin Akyurek]

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.