How to get Count of each Column value of table?

Msvba searches

• QUESTION: Greetings I hope you are well, and thank you for answering my previous questions. I have a follow-up question: For the attached picture, I want to be able to develop code that can find the name in column A and then select all the activities in column C, and return the sum of values in columns E:blank column (another function defines the startcol and endcol). This is a step further than the question you answered for me on July1, in which we simply looked for values in column C to match a given value and summed all the columns from startcol to endcol (code below). Sub SumofALLCommitteesforDates() 'sum of all committees including the non-committee Dim lastcolumn As Long, i As Long, r As Range Dim allsum As Double 'find the coumns with the start and end date lastcolumn = Sheet3.Cells(2, Columns.Count).End(xlToLeft).column For i = 4 To lastcolumn Set r = Sheet3.Cells(2, i) If r.Value = Sheet4.Range("date1").Value Then startcol = i If r.Value = Sheet4.Range("date2").Value Then endcol = i Next ' find the rows with the category and as each is found, accumulate the ' the sum for that row For i = 4 To 135 ' Sheet3.Range("C4:C9999").End(xlDown).row If Sheet3.Cells(i, "C").Value <> Sheet4.Range("selectedcommittee") Then Set r1 = Sheet3.Cells(i, startcol).Resize(1, endcol - startcol + 1) allsum = allsum + Application.Sum(r1) End If Next MsgBox "sum is " & allsum - NotCommSum End Sub I know I have to add some more if/then statements in the second For loop that will first identify the range with the correct name, then identify the items in column c, then sum all the values for the identified columns from startcol to endcol - I hope you can help, you were a tremendous help the first go-round... ANSWER: Qasim, based on your picture and your description of what you want to do, this worked for me. >first identify the range with the correct name, then identify the items in column c that statement doesn't match your original description: >I want to be able to develop code that can find the name in column A and then select all the activities in column C, and return the sum of values in columns E:blank column I don't see column C really playing a role here. this code adds up all the rows included in the merged cells for Column A where the name is found and between and including the start and end dates specified in the defined sheet level names (Sheet4) Date1 and Date2. Hopefully that is what you want. Sub AABBCC() Dim lastcolumn As Long, i As Long, r As Range Dim allsum As Double, startcol As Long, endcol As Long Dim rw As Long, r1 As Range, r2 As Range, s As String 'find the coumns with the start and end date lastcolumn = Sheet3.Cells(1, Columns.Count).End(xlToLeft).Column 'For i = 4 To lastcolumn For i = 5 To lastcolumn ' from your picture, it appears that the dates start in column E and ' are in row 1 so see changes in the previous and next line Set r = Sheet3.Cells(1, i) ' assume sheet3 is the sheet in the picture 'Set r = Sheet3.Cells(2, i) If r.Value = Sheet4.Range("date1").Value Then startcol = i If r.Value = Sheet4.Range("date2").Value Then endcol = i Next ' find the rows with the category and as each is found, accumulate the ' the sum for that row For i = 4 To 135 Set r1 = Sheet3.Cells(i, 1).MergeArea rw = r1(1).Row If rw = i Then s = LCase(Sheet3.Cells(i, 1).Value) If LCase("Quasim") = s Then ' Next If Not r2 Is Nothing Then MsgBox "Total is " & Application.Sum(r2) Else MsgBox "Problems finding range" End If End Sub ---------- FOLLOW-UP ---------- QUESTION: Thanks, Tom. I apologize if my question was a bit unclear. What I would like to do is to identify the values in column C for each name and then get the sum of those values for the selected dates. For example, Qasim has the following committee names selected: Admin, Admin, CCEC, CCEC, SG2, Programmatic Support, Backfill Committee Support, Systems Support, Face to Face Support, Time not on CTSA, and IPP (for a total of 11 rows). Each PM will have different values in Column C next to their names. I want to be able to pick up these values (committees), and then for the date range selected, output each individual committee's total hours. Input from the end user will be: select date range (numerical value) Select PM Output will be: PM name, 11 committee names, and total hours for the date range. Right now, the way you explained your code, the output will be the sum of all hours for the PM - not hours broken down by each committee. ANSWER: Quasim, for your example, there are 11 rows, but only 9 unique committee names. if you want one sum for each row regardless of whether there are duplicate committee names, you can just do this with a formula in the next available column. You don't need any code. You would then have the totals for all names. If you think you need code, then where does this output go and exactly/specifically what is is supposed to look like. this is pretty meaningless if you want 11 results: >Output will be: PM name, 11 committee names, and total hours for the date range. how to you want it arranged. Where do you want it placed. ---------- FOLLOW-UP ---------- QUESTION: The output will be a separate table in a separate sheet (for this exercise, we can make it sheet5.range("a40:a51") for the 11 committee names and sheet5.range("b40:b51") for the total hours for the selected date range. There is unlikely to be an occurrence in the real world where the rows will be duplicated within each PM. Different PMs may have hours associated with the same committee, but no one PM will have duplicate committee names. The output may look like this: Column A Column B Row 39 Committee Name Hours Row 40 Committee1 5 Row 41 Committee2 10 Row 42 Committee3 10 Row 43 Committee4 30 Row 45 Committee5 40 Row 46 Committee6 15 ...

• Answer:

  Qasim, Sub AABBCC() Dim lastcolumn As Long, i As Long, r As Range Dim allsum As Double, startcol As Long, endcol As Long Dim rw As Long, r1 As Range, r2 As Range, s As String Dim rw5 As Long, rw2 As Range 'find the coumns with the start and end date lastcolumn = Sheet3.Cells(1, Columns.Count).End(xlToLeft).Column 'For i = 4 To lastcolumn For i = 5 To lastcolumn ' from your picture, it appears that the dates start in column E and ' are in row 1 so see changes in the previous and next line Set r = Sheet3.Cells(1, i) ' assume sheet3 is the sheet in the picture 'Set r = Sheet3.Cells(2, i) If r.Value = Sheet4.Range("date1").Value Then startcol = i If r.Value = Sheet4.Range("date2").Value Then endcol = i Next ' find the rows with the category and as each is found, accumulate the ' the sum for that row For i = 4 To 135 Set r1 = Sheet3.Cells(i, 1).MergeArea rw = r1(1).Row If rw = i Then s = LCase(Sheet3.Cells(i, 1).Value) If LCase("Quasim") = s Then ' Next rw5 = 39 Sheet5.Cells(rw5, "A").Value = "Committee Name" Sheet5.Cells(rw5, "B").Value = "Hours" If Not r2 Is Nothing Then For Each rw2 In r2.Rows rw5 = rw5 + 1 Sheet5.Cells(rw5, "A").Value = Sheet3.Cells(rw2.Row, "C").Value Sheet5.Cells(rw5, "B").Value = Application.Sum(rw2) Next Else MsgBox "Problems finding range" End If End Sub