Sum Function

[coreyjames]

I am working on a project where I have procedure names in column I. In K1 I want to put the number of case so if I have four procedures listed I want the number to say 4. The problem is if I have a procedure mentioned twice I only want to count it once. Please see below

Hernia Repair
Colectomy Hemi
Colon Resection
Bowel Resection
Hernia Repair


So I have 5 procedures listed but I only want to return a value of 4 because Hernia repair is mentioned twice. Thanks!

 

[Karter705]

If I understand correctly, you just want to count the unique items in the column? Something like this should work;

Sub countUnique()

Dim tmpArray() As Variant
Dim procedure As Variant
Dim countUnique As Long
Dim i As Integer
Dim alreadyExists As Boolean


    Set ProcedureNames = Sheet1.Range("I:I")
    
countUnique = 0

    For Each procedure In ProcedureNames
        alreadyExists = False
            For i = 1 To countUnique
                If procedure = tmpArray(i) Then
                    alreadyExists = True
                    Exit For
                End If
            Next i

        If Not alreadyExists And Not IsEmpty(procedure) Then
            countUnique = countUnique + 1
            ReDim Preserve tmpArray(countUnique)
            tmpArray(countUnique) = procedure
        End If
    Next procedure
    
Sheet1.Cells(1, 11) = countUnique
                

End Sub

 

[coreyjames]

Where does this produce the results? Is there a way to do this in real time with a formula? I have a pivot and as I manipulate it my list changes.

 

[Karter705]

Where does this produce the results? Is there a way to do this in real time with a formula? I have a pivot and as I manipulate it my list changes.

Hmmm... you can only do a formula if you know the range, like:

{=SUM(IF(I1:I10<>"",1/COUNTIF(I1:I10,I1:I10)))}



You can also use named ranges, so maybe you could create a dynamic named range for the list of procedures: http://www.ozgrid.com/Excel/DynamicRanges.htm



Edit: Or if you know that it will never be more than a certain number, you could just set it to that range:

{=SUM(IF(I1:I200<>"",1/COUNTIF(I1:I200,I1:I200)))} for example.

Edit2: FYI the {} are to indicate it is an array formula, so you would enter the formula as =SUM(IF(I1:I200<>"",1/COUNTIF(I1:I200,I1:I200))) and then hit ctrl+shift+enter http://www.ozgrid.com/Excel/arrays.htm

 

[coreyjames]

This formula almost works. The answer should be 5 but it says 6. I think it is counting my vlookups that do not return a value as one. Is there a way to modify this formula to ignore the "" that the vlookup returns?



=SUM(IF(FREQUENCY(IF(LEN(G7:G500)>0,MATCH(G7:G500,G7:G500,0),""), IF(LEN(G7:G500)>0,MATCH(G7:G500,G7:G500,0),""))>0,1))

 

[Marcelo Branco]

Hi,

Assuming your procedures names in column I, maybe this array-formula

=SUM(IF(FREQUENCY(IF(I1:I100<>"",MATCH("~"&I1:I100,I1:I100&"",0)),ROW(I1:I100)-ROW(I1)+1),1))
confirmed with Ctrl+Shift+Enter

HTH

M

 

[T. Valko]

This formula almost works. The answer should be 5 but it says 6. I think it is counting my vlookups that do not return a value as one. Is there a way to modify this formula to ignore the "" that the vlookup returns?



=SUM(IF(FREQUENCY(IF(LEN(G7:G500)>0,MATCH(G7:G500,G7:G500,0),""), IF(LEN(G7:G500)>0,MATCH(G7:G500,G7:G500,0),""))>0,1))

If you only have ~500 rows of data try this...

=SUMPRODUCT((G7:G500<>"")/COUNTIF(G7:G500,G7:G500&""))

 

[coreyjames]

If you only have ~500 rows of data try this...

=SUMPRODUCT((G7:G500<>"")/COUNTIF(G7:G500,G7:G500&""))

This formula also gives me an answer of 6 for some reason.

 

[Marcelo Branco]

Maybe

=SUM(IF(FREQUENCY(IF(G7:G500<>"",MATCH("~"&G7:G500,G7:G500&"",0)),ROW(G7:G500)-ROW(G7)+1),1))
confirmed with Ctrl+Shift+Enter

HTH

M.

 

[coreyjames]

Maybe

=SUM(IF(FREQUENCY(IF(G7:G500<>"",MATCH("~"&G7:G500,G7:G500&"",0)),ROW(G7:G500)-ROW(G7)+1),1))
confirmed with Ctrl+Shift+Enter

HTH

M.

Thank you everyone for your help. The problem was my vlookup in the column. I had it to return a 0 instead of "". Once I made this change it calculated properly. Thanks again for all of the suggestions I really appreciate it.