Help using CountA or ??

Cowichandave · Oct 23, 2023

Each number in Column A is a person in Col B. I need a count for total entries in Col D to G for each person. Would CountA work. I am lost on this one. Any help would be appreciated.

A	B	C	D	E	F	G

1	BobT
	1	49N01
	2	BAS16
	3	DRR03		150.00
	4	ATB19	339.99
	5	AUM02
	6	BAY05
	7	BUI05			199.99
	8	CAC03	399.94		639.90	2339.96
	9	CAS21
	10	COM45		140.00
	11	CONT07		298.93	119.77
			2	3	3	1

2	BonR
	1	15520
	2	CRE06	439.00
	3	CAP21			195.00
	4	CARL07	215.47
	5	CHE11				100.00
			2	0	1	1

3	ChrisB
	1	CHI18				114.50
	2	4TH01	2066.89		1129.94	149.70
	3	AND12
	4	15520	86.20		819.20
	5	ATC21
	6	CAN91			1095.00
	7	CAO01	259.56	1691.00
	8	CAR32		1316.93
	9	CSC07	490.00		490.00
	10	COM44	909.20		573.54	975.30
	11	DOW04		568.97
	12	ENB03
	13	FAL04
	14	INF33			303.24
	15	MAS13	515.52	1740.35	711.64	769.28
			6	4	7	4

Muhammad_Usman · Oct 23, 2023

1235.xlsm

A

B

C

D

E

F

G

1

BobT

2

1

49N01

3

2

BAS16

4

3

DRR03

150.00

5

4

ATB19

339.99

6

5

AUM02

7

6

BAY05

8

7

BUI05

199.99

9

8

CAC03

399.94

639.90

2339.96

10

9

CAS21

11

10

COM45

140.00

12

11

CONT07

298.93

119.77

13

2

3

1

14

15

2

BonR

16

1

15520

17

2

CRE06

439.00

18

3

CAP21

195.00

19

4

CARL07

215.47

20

5

CHE11

100.00

21

2

0

1

22

23

3

ChrisB

24

1

CHI18

114.50

25

2

4TH01

2066.89

1129.94

149.70

26

3

AND12

27

4

15520

86.20

819.20

28

5

ATC21

29

6

CAN91

1095.00

30

7

CAO01

259.56

1691.00

31

8

CAR32

1316.93

32

9

CSC07

490.00

33

10

COM44

909.20

573.54

975.30

34

11

DOW04

568.97

35

12

ENB03

36

13

FAL04

37

14

INF33

303.24

38

15

MAS13

515.52

1740.35

711.64

769.28

39

Sheet4

Cell Formulas
Range	Formula
D13:G13	D13	=COUNTA(D2:D12)
D21:G21	D21	=COUNTA(D16:D20)
D39:G39	D39	=COUNTA(D24:D38)

Cowichandave · Oct 23, 2023

Muhammad_Usman said:
1235.xlsm
A B C D E F G
1 1 BobT
2 1 49N01
3 2 BAS16
4 3 DRR03 150.00
5 4 ATB19 339.99
6 5 AUM02
7 6 BAY05
8 7 BUI05 199.99
9 8 CAC03 399.94 639.90 2339.96
10 9 CAS21
11 10 COM45 140.00
12 11 CONT07 298.93 119.77
13 2 3 3 1
14
15 2 BonR
16 1 15520
17 2 CRE06 439.00
18 3 CAP21 195.00
19 4 CARL07 215.47
20 5 CHE11 100.00
21 2 0 1 1
22
23 3 ChrisB
24 1 CHI18 114.50
25 2 4TH01 2066.89 1129.94 149.70
26 3 AND12
27 4 15520 86.20 819.20
28 5 ATC21
29 6 CAN91 1095.00
30 7 CAO01 259.56 1691.00
31 8 CAR32 1316.93
32 9 CSC07 490.00 490.00
33 10 COM44 909.20 573.54 975.30
34 11 DOW04 568.97
35 12 ENB03
36 13 FAL04
37 14 INF33 303.24
38 15 MAS13 515.52 1740.35 711.64 769.28
39 6 4 7 4
Sheet4
Cell Formulas
Range Formula
D13:G13 D13 =COUNTA(D2:D12)
D21:G21 D21 =COUNTA(D16:D20)
D39:G39 D39 =COUNTA(D24:D38)

Muhammad_Usman · Oct 23, 2023

Is this working for you?

Cowichandave · Oct 23, 2023

Actually wanting a vba solution based on Range A and coming up with a CountA for Col D to G for each Range A

Cowichandave · Oct 23, 2023

Cowichandave said:
Actually wanting a vba solution based on Range A and coming up with a CountA for Col D to G for each Range A

This is part of a larger project that assembles data with a Pivot Table. There are 50 people in Range A

Muhammad_Usman · Oct 23, 2023

Cowichandave said:
Actually wanting a vba solution based on Range A and coming up with a CountA for Col D to G for each Range A

VBA Code:

Sub InsertRowsAndCountNonEmptyCells()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim SubCode As String
    Dim NewRow As Long
    Dim CountData1 As Long, CountData2 As Long, CountData3 As Long, CountData4 As Long
    Dim i As Long

    ' Set the worksheet where your data is located
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Update with your sheet name

    ' Initialize the first new row
    NewRow = 2

    ' Find the last row with data in column B
    LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Loop through the data
    For i = 2 To LastRow ' Assuming data starts from row 2
        SubCode = ws.Cells(i, 2).Value
        If SubCode <> ws.Cells(i - 1, 2).Value And SubCode <> "" Then
            ' Insert a new row when a new sub code is encountered
            ws.Rows(i + NewRow - 1).Insert Shift:=xlDown

            ' Count non-empty cells in columns Data1 to Data4 for the new row
            CountData1 = Application.WorksheetFunction.CountA(ws.Range("D" & i & ":D" & i))
            CountData2 = Application.WorksheetFunction.CountA(ws.Range("E" & i & ":E" & i))
            CountData3 = Application.WorksheetFunction.CountA(ws.Range("F" & i & ":F" & i))
            CountData4 = Application.WorksheetFunction.CountA(ws.Range("G" & i & ":G" & i))
            
            ' Display the counts in the new row's corresponding cells
            ws.Cells(i + NewRow, 4).Value = CountData1
            ws.Cells(i + NewRow, 5).Value = CountData2
            ws.Cells(i + NewRow, 6).Value = CountData3
            ws.Cells(i + NewRow, 7).Value = CountData4
            
            ' Increment the new row counter
            NewRow = NewRow + 1
        End If
    Next i
End Sub

Here Is VBA Code Just Change the name of sheet4 to your sheet name

Cowichandave · Oct 23, 2023

Muhammad_Usman said:

VBA Code:

Sub InsertRowsAndCountNonEmptyCells()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim SubCode As String
    Dim NewRow As Long
    Dim CountData1 As Long, CountData2 As Long, CountData3 As Long, CountData4 As Long
    Dim i As Long

    ' Set the worksheet where your data is located
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Update with your sheet name

    ' Initialize the first new row
    NewRow = 2

    ' Find the last row with data in column B
    LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Loop through the data
    For i = 2 To LastRow ' Assuming data starts from row 2
        SubCode = ws.Cells(i, 2).Value
        If SubCode <> ws.Cells(i - 1, 2).Value And SubCode <> "" Then
            ' Insert a new row when a new sub code is encountered
            ws.Rows(i + NewRow - 1).Insert Shift:=xlDown

            ' Count non-empty cells in columns Data1 to Data4 for the new row
            CountData1 = Application.WorksheetFunction.CountA(ws.Range("D" & i & ":D" & i))
            CountData2 = Application.WorksheetFunction.CountA(ws.Range("E" & i & ":E" & i))
            CountData3 = Application.WorksheetFunction.CountA(ws.Range("F" & i & ":F" & i))
            CountData4 = Application.WorksheetFunction.CountA(ws.Range("G" & i & ":G" & i))
          
            ' Display the counts in the new row's corresponding cells
            ws.Cells(i + NewRow, 4).Value = CountData1
            ws.Cells(i + NewRow, 5).Value = CountData2
            ws.Cells(i + NewRow, 6).Value = CountData3
            ws.Cells(i + NewRow, 7).Value = CountData4
          
            ' Increment the new row counter
            NewRow = NewRow + 1
        End If
    Next i
End Sub

Here Is VBA Code Just Change the name of sheet name

Muhammad_Usman said:

VBA Code:

Sub InsertRowsAndCountNonEmptyCells()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim SubCode As String
    Dim NewRow As Long
    Dim CountData1 As Long, CountData2 As Long, CountData3 As Long, CountData4 As Long
    Dim i As Long

    ' Set the worksheet where your data is located
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Update with your sheet name

    ' Initialize the first new row
    NewRow = 2

    ' Find the last row with data in column B
    LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Loop through the data
    For i = 2 To LastRow ' Assuming data starts from row 2
        SubCode = ws.Cells(i, 2).Value
        If SubCode <> ws.Cells(i - 1, 2).Value And SubCode <> "" Then
            ' Insert a new row when a new sub code is encountered
            ws.Rows(i + NewRow - 1).Insert Shift:=xlDown

            ' Count non-empty cells in columns Data1 to Data4 for the new row
            CountData1 = Application.WorksheetFunction.CountA(ws.Range("D" & i & ":D" & i))
            CountData2 = Application.WorksheetFunction.CountA(ws.Range("E" & i & ":E" & i))
            CountData3 = Application.WorksheetFunction.CountA(ws.Range("F" & i & ":F" & i))
            CountData4 = Application.WorksheetFunction.CountA(ws.Range("G" & i & ":G" & i))
           
            ' Display the counts in the new row's corresponding cells
            ws.Cells(i + NewRow, 4).Value = CountData1
            ws.Cells(i + NewRow, 5).Value = CountData2
            ws.Cells(i + NewRow, 6).Value = CountData3
            ws.Cells(i + NewRow, 7).Value = CountData4
           
            ' Increment the new row counter
            NewRow = NewRow + 1
        End If
    Next i
End Sub

Here Is VBA Code Just Change the name of sheet4 to your sheet name

That does not work. Here are the results

1	BobT

	1	49N01	0.00	0.00	0.00	0.00
	2	BAS16

	3	DRR03	1.00	1.00	1.00	1.00

	4	ATB19	0.00	0.00	0.00	0.00
	5	AUM02

	6	BAY05	1.00	1.00	1.00	1.00
	7	BUI05			199.99

	8	CAC03	1.00	1.00	1.00	1.00

	9	CAS21	0.00	0.00	0.00	0.00
	10	COM45		140.00

	11	CONT07	1.00	1.00	1.00	1.00

			0	0	1	0


2	BON07		1.00	1.00	1.00	1.00
	1	15520

	2	CRE06	1.00	1.00	1.00	1.00

	3	CAP21	0.00	1.00	0.00	0.00
	4	CARL07	215.47

	5	CHE11	1.00	1.00	1.00	1.00


3	ChrisB
	1	CHI18				114.50

	2	4TH01	1.00	1.00	1.00	1.00

	3	AND12	0.00	0.00	0.00	0.00
	4	15520	86.20		819.20

	5	ATC21	1.00	1.00	1.00	1.00
	6	CAN91			1095.00

	7	CAO01	1.00	1.00	1.00	1.00

	8	CAR32	1.00	0.00	0.00	0.00
	9	CSC07	490.00		490.00

	10	COM44	1.00	1.00	1.00	1.00
	11	DOW04		568.97
	12	ENB03

	13	FAL04	0.00	0.00	0.00	0.00

	14	INF33	0.00	0.00	0.00	1.00
	15	MAS13	515.52	1740.35	711.64	769.28

			1	1	1	1

Alex Blakenburg · Oct 23, 2023

Is your data in Post #1 your actual starting point ? ie do you have 2 blank rows between the end of BobT and the start of BonR or does the macro need to insert them.
Is the first item "1" in row 2 of column A ?
Do you want the formulas in the final output or just the resulting values ?

Cowichandave · Oct 23, 2023

Alex Blakenburg said:
Is your data in Post #1 your actual starting point ? ie do you have 2 blank rows between the end of BobT and the start of BonR or does the macro need to insert them.
Is the first item "1" in row 2 of column A ?
Do you want the formulas in the final output or just the resulting values ?

This is the data. I have added the row and column numbers. There are 2 blank lines already between each employee. No blank lines should be added.. A1 is the employee. B2 is the start of the data. Col A is the employee number and it varies up to about 50
I just need the resulting value in col D to G which for employee #1 is 2 , 3,3 ,1. For employee 1 the total should be in row 13. For employee2 in row 21 and so on

	A	B	C	D	E	F	G

1	1	BobT
2		1	49N01
3		2	BAS16
4		3	DRR03		150.00
5		4	ATB19	339.99
6		5	AUM02
7		6	BAY05
8		7	BUI05			199.99
9		8	CAC03	399.94		639.90	2339.96
10		9	CAS21
11		10	COM45		140.00
12		11	CONT07		298.93	119.77
13
14
15	2	BON07
16		1	15520
17		2	CRE06	439.00
18		3	CAP21			195.00
19		4	CARL07	215.47
20		5	CHE11				100.00
21
22
23	3	ChrisB
24		1	CHI18				114.50
25		2	4TH01	2066.89		1129.94	149.70
26		3	AND12
27		4	15520	86.20		819.20
28		5	ATC21
29		6	CAN91			1095.00
30		7	CAO01	259.56	1691.00
31		8	CAR32		1316.93
32		9	CSC07	490.00		490.00
33		10	COM44	909.20		573.54	975.30
34		11	DOW04		568.97
35		12	ENB03
36		13	FAL04
37		14	INF33			303.24
38		15	MAS13	515.52	1740.35	711.64	769.28

Help using CountA or ??

Cowichandave

New Member

Excel Facts

Muhammad_Usman

Active Member

Cowichandave

New Member

Muhammad_Usman

Active Member

Cowichandave

New Member

Cowichandave

New Member

Muhammad_Usman

Active Member

Cowichandave

New Member

Alex Blakenburg

MrExcel MVP

Cowichandave

New Member

Forum statistics

Share this page

Help using CountA or ??

New Member

Excel Facts

Active Member

New Member

Active Member

New Member

New Member

Active Member

New Member

MrExcel MVP

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock