Sum Frequencies in VBA

  • Good Morning All!


    I hope I have an easy one for you this morning. I've got about 96k rows of policy numbers and 12 columns starting in column B (Jan-Dec). If a policyholder called within that month it displays how many times they called. I'm not concerned with how many calls they made, only if there is a value > 0 within that cell. The goal here is to sum the clusters of consecutive months they called in. If they call in just in January but not in February, I don't care. If they call in January and February, but not March, that would be considered 1 cluster. If they called in January, February, March, not in April, not in May, called in June, not called in July, called in August, called in September, didn't call us the rest of the year, the sum of clusters would be 2 (Jan-Mar) and (Aug-Sept), don't care about the 1 off month of June where they called since it's not a cluster. I've been trying to incorporate code similar to "=SUM(--((FREQUENCY(IF(A2:H2>=50, COLUMN(A2:H2)),IF(A2:H2<50,COLUMN(A2:H2))))>=3))" granted this series of code is for a completely different project unrelated, but I feel like it could be applicable by changing the cells and values (50 and 3).


    I hope this goal makes sense and someone can help. If you need anymore information, let me know. The simpler the better.

  • So you tried something like this Array* formula?


    =SUM(--((FREQUENCY(IF(A2:L2>0, COLUMN(A2:M2)),IF(A2:L2=0,COLUMN(A2:L2))))>1))


    [arf]*[/arf]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • So you tried something like this Array* formula?


    =SUM(--((FREQUENCY(IF(A2:L2>0, COLUMN(A2:M2)),IF(A2:L2=0,COLUMN(A2:L2))))>1))


    [arf]*[/arf]


    That's essentially what I'm trying to enter into my VBA code, however when I'm putting it in, it doesn't like the "IF" statement within the Frequency function

  • Not sure what you mean by "doesn't like". What does your vba code look like and what error are you getting?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • I don't see the formula in the code you show.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • The way to add a formula to the spreadsheet from vba is to use .formulaarray


    e.g.


    Code
    1. Range("M2").FormulaArray = "[COLOR=#0000FF]=SUM(--((FREQUENCY(IF(A2:L2>0, COLUMN(A2:M2)),IF(A2:L2=0,COLUMN(A2:L2))))>1))"[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Unfortunately, I don't think I follow. I still get the attached error highlighting the underlined IF expression.


    The statement in your picture does not match my suggestino...

    Where there is a will there are many ways. Finding one that works for you is the challenge!