Counting the # of values repeatedly in a column

[levanoj]

Is there a code that can provide the sum of the # of values in a particular column? So for example, I have a spreadsheet that looks like this:

MODLBUTN(Test2).xlsx
	A	B	C	D
1	LAC	Group	Count
2	1002	Mort	2
3	1002	Muni	3
4	1002	Deriv	1
5	1002	EM	1
6
7	1003	Mort	1
8	1003	Muni	1
9	1003	EM	1
10
11	1005	Mort	1
12	1005	Deriv	1
13
14	1010	Muni	55
15
16	1011	Muni	57
17
Sheet3

And I'd like a code that will prompt me to select a column (A,B or C) and provide the some in the blank cells of that column of the # of values directly above it and provide a result like this:

MODLBUTN(Test2).xlsx
	A	B	C	D
1	LAC	Group	Count
2	1002	Mort	2
3	1002	Muni	3
4	1002	Deriv	1
5	1002	EM	1
6		4
7	1003	Mort	1
8	1003	Muni	1
9	1003	EM	1
10		3
11	1005	Mort	1
12	1005	Deriv	1
13		2
14	1010	Muni	55
15		1
16	1011	Muni	57
17		1
Sheet3

Except for the 1st sum result (row 6) it wouldn't count the header (Group) as a value.

 

[seenfresh]

What about utilizing a Pivot Table? Group by LAC

 

[Marcelo Branco]

Hi,

Maybe something like

Sub Count()
    Dim lastRow As Long, rng As Range
    
    With Sheets("Sheet3")
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
        Set rng = .Range("B1:B" & lastRow).SpecialCells(xlCellTypeBlanks)
        rng.Formula = "=Countif(" & .Range("A2:A" & lastRow).Address _
        & "," & rng.Cells(1, 1).Offset(-1, -1).Address(False, False) & ")"
    End With
End Sub

HTH

M.

 

[levanoj]

The code worked perfectly, thanks Marcelo

 

[Marcelo Branco]

The code worked perfectly, thanks Marcelo

You are welcome and tks for the feedback