Help with COUNTIFS

1legin · Apr 17th 2018

[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

**alansidman** · Apr 17th 2018

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

Code

Sub Freq()
    Dim c As Range, rng As Range
    Dim lr As Long, x As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("B2:F" & lr)
    For i = 2 To lr
        x = 0
        For Each c In rng
            If c = Range("H" & i) Then
                x = x + 1
            End If
        Next c
        Range("I" & i) = x
    Next i


    MsgBox "complete"


End Sub

Display More

1legin · Apr 18th 2018

Hi Alan,

Thanks for the answer to my question,How would i implement this ? as i am no good with VBA.

Thanks again

Nigel

**alansidman** · Apr 18th 2018

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

1legin · Apr 18th 2018

Works great Thanks Alan

Help with COUNTIFS

Participate now!

Share