Thread: Sum Frequencies in VBA in Excel
View Single Post
 
Old 04-30-2018, 09:51 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
  
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default
Using NBVC's formula, adjusted for the columns you actually want, and the sample workbook you posted over there....see if this works

Code:
Sub Clusters()
Dim lr As Long
With Sheets("Sheet3")
    lr = .Cells(2, "A").End(xlDown).Row
    .Range("Q2").FormulaArray = "=SUM(--((FREQUENCY(IF(B2:M2>0, COLUMN(B2:M2)),IF(B2:M2=0,COLUMN(B2:M2))))>1))"
    .Range("Q2").AutoFill Destination:=Range("Q2:Q" & lr)
End With
End Sub
Reply With Quote