Alter Formula for unique value to sum only visible cells

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
758
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

Well-known Member
Joined
Jul 23, 2010
Messages
758
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 ???
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

Well-known Member
Joined
Jul 23, 2010
Messages
758
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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

Well-known Member
Joined
Jul 23, 2010
Messages
758
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....
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
758
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 #REFP.O.DATE
9582212331-Oct-13
9582512331-Oct-13
9582812331-Oct-13
9583012331-Oct-13
9583112331-Oct-13
4004154112727-Sep-13
4004322712713-Feb-14
400420451297-Nov-13
12943122 A13011-Dec-13
12943122 B13011-Dec-13
67257113121-Nov-13
7811813219-Dec-13
7818613219-Dec-13
7858113219-Dec-13
7642413320-Jan-14
7653513320-Jan-14
7654613320-Jan-14

<tbody>
</tbody>




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

Filter ------>7
P.O #REFP.O.DATE
4004154112727-Sep-13
4004322712713-Feb-14

<tbody>
</tbody>
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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.
 

Forum statistics

Threads
1,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top