Help with COUNTIFS

  • [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="class: xl64, width: 64"]A[/TD]
    [TD="class: xl64, width: 64"]B[/TD]
    [TD="class: xl64, width: 64"]C[/TD]
    [TD="class: xl64, width: 64"]D[/TD]
    [TD="class: xl64, width: 64"]E[/TD]
    [TD="class: xl64, width: 64"]F[/TD]
    [TD="class: xl64, width: 31"]G[/TD]
    [TD="class: xl64, width: 64"]H[/TD]
    [TD="class: xl64, width: 64"]I[/TD]

    [/tr]


    [tr]


    [TD="class: xl64"]Row No.[/TD]
    [TD="class: xl64"]N1[/TD]
    [TD="class: xl64"]N2[/TD]
    [TD="class: xl64"]N3[/TD]
    [TD="class: xl64"]N4[/TD]
    [TD="class: xl64"]N5[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl64"]No.'s[/TD]
    [TD="class: xl64"]Enter No.[/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]1[/TD]
    [TD="class: xl63"]5[/TD]
    [TD="class: xl63"]7[/TD]
    [TD="class: xl63"]10[/TD]
    [TD="class: xl63"]17[/TD]
    [TD="class: xl63"]34[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]1[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]2[/TD]
    [TD="class: xl63"]15[/TD]
    [TD="class: xl63"]18[/TD]
    [TD="class: xl63"]21[/TD]
    [TD="class: xl63"]30[/TD]
    [TD="class: xl63"]34[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]2[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]3[/TD]
    [TD="class: xl63"]4[/TD]
    [TD="class: xl63"]14[/TD]
    [TD="class: xl63"]15[/TD]
    [TD="class: xl63"]22[/TD]
    [TD="class: xl63"]29[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]3[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]4[/TD]
    [TD="class: xl63"]1[/TD]
    [TD="class: xl63"]11[/TD]
    [TD="class: xl63"]19[/TD]
    [TD="class: xl63"]20[/TD]
    [TD="class: xl63"]37[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]4[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]5[/TD]
    [TD="class: xl63"]3[/TD]
    [TD="class: xl63"]16[/TD]
    [TD="class: xl63"]18[/TD]
    [TD="class: xl63"]21[/TD]
    [TD="class: xl63"]36[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]5[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]6[/TD]
    [TD="class: xl63"]2[/TD]
    [TD="class: xl63"]12[/TD]
    [TD="class: xl63"]36[/TD]
    [TD="class: xl63"]37[/TD]
    [TD="class: xl63"]38[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]6[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]7[/TD]
    [TD="class: xl63"]2[/TD]
    [TD="class: xl63"]4[/TD]
    [TD="class: xl63"]10[/TD]
    [TD="class: xl63"]28[/TD]
    [TD="class: xl63"]35[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]7[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]8[/TD]
    [TD="class: xl63"]8[/TD]
    [TD="class: xl63"]29[/TD]
    [TD="class: xl63"]30[/TD]
    [TD="class: xl63"]31[/TD]
    [TD="class: xl63"]32[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]8[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]9[/TD]
    [TD="class: xl63"]7[/TD]
    [TD="class: xl63"]15[/TD]
    [TD="class: xl63"]18[/TD]
    [TD="class: xl63"]19[/TD]
    [TD="class: xl63"]34[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]9[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]10[/TD]
    [TD="class: xl63"]18[/TD]
    [TD="class: xl63"]19[/TD]
    [TD="class: xl63"]29[/TD]
    [TD="class: xl63"]33[/TD]
    [TD="class: xl63"]37[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]10[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]11[/TD]
    [TD="class: xl63"]2[/TD]
    [TD="class: xl63"]4[/TD]
    [TD="class: xl63"]25[/TD]
    [TD="class: xl63"]32[/TD]
    [TD="class: xl63"]38[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]11[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]12[/TD]
    [TD="class: xl63"]6[/TD]
    [TD="class: xl63"]9[/TD]
    [TD="class: xl63"]22[/TD]
    [TD="class: xl63"]26[/TD]
    [TD="class: xl63"]31[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]12[/TD]
    [TD="class: xl64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]13[/TD]
    [TD="class: xl63"]3[/TD]
    [TD="class: xl63"]13[/TD]
    [TD="class: xl63"]19[/TD]
    [TD="class: xl63"]33[/TD]
    [TD="class: xl63"]37[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl63"]13[/TD]
    [TD="class: xl64"]
    [/TD]

    [/tr]


    [/TABLE]
    Hi all,


    I am trying to work out the frequancy for numbers in columns B to F.
    So there is a total of 500 rows "B1:F500", i would like the answers to be in column I, but
    instead of having results for all rows, i would like to enter a number in cell "Enter No."
    and have the results for that many rows.
    Not sure if this is possible with countifs or drop down box etc ..


    Thanks


    Nigel

  • Here is a VBA solution that works for the example you provided


  • How to install your new code
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


    To run the Excel VBA code:
    Press Alt-F8 to open the macro list
    Select a macro in the list
    Click the Run button