Consulting

Results 1 to 5 of 5

Thread: Filter and sum the column values

  1. #1
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location

    Filter and sum the column values

    Dear I have a consolidated history.xlsx Excel file. In thison consolidated histy sheet I need to sum the column J (Manday) values after deselecting (holiday & leave) on column K ( Nature of visit). The filter condition is it has to de-select only holiday & leave. And the sum value should be display in sheet2 B3 cell.

    I am attaching my excel file here.


    Pls give me the program for the above condition
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Two different ways in the attached in cells B3 and B6.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    Hi p45cal

    Thanks for your reply. The formula given by you is working as per my requirement.

    I have one more doubt.

    Is it possible to add the same formula on VBA code.


  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Sub blah()
    Set CHData = Sheets("Consolidated History").Range("A1").CurrentRegion
    Set CHData = Intersect(CHData, CHData.Offset(1)) 'just the databody, not the headers.
    
    Sheets("Sheet2").Range("B3").Value =Application.WorksheetFunction.SumIfs(CHData.Columns("J"), CHData.Columns("K"), "<>Leave", CHData.Columns("K"), "<>Holiday")
    'longhand:
    'Z = Application.WorksheetFunction.SumIfs(CHData.Columns("J"), CHData.Columns("K"), "<>Leave", CHData.Columns("K"), "<>Holiday")
    'MsgBox Z
    'Sheets("Sheet2").Range("B3").Value = Z
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    Hi p45cal,

    Thanks for your code. It is working great as per my conditions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •