Consulting

Results 1 to 5 of 5

Thread: COUNTIFS in VBA with data on multiple sheets

  1. #1

    COUNTIFS in VBA with data on multiple sheets

    Hello All,

    First time in this forum and new to VBA, so I appreciate any help I can get.

    Trying to create code for a COUNTIFS function to return values from another worksheet.
    The two worksheets being used are:
    1. Central Summary: where my value is to be returned and that has the "text" i want to search in the 2nd worksheet
    2. Central Data: the data sheet i want referenced and counted.


    I want the script to return value into Cell C16 of my "Central Summary" worksheet;

    I need the code to search column M in my "Central Data" worksheet for text in Cell A23 from the "Central Summary" worksheet, then using that criteria, search the "Central Data" worksheet again for the words "On Hold" in Column A and return the sum of those occurrences.

    The current working formula is below

    =COUNTIFS('Central Data'!M:M,'Central Summary'!A23,'Central Data'!A:A,"On Hold")

    Thank you in advance for all the help.
    A.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Maybe something like:

    =CountIfs('Central Data'!A:A,COUNTIFS('Central Data'!M:M,A23),"On Hold")
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by SamT View Post
    =CountIfs('Central Data'!A:A,COUNTIFS('Central Data'!M:M,A23),"On Hold")
    Put that in square brackets and you should have your vba code:
    Sheets("Central Summary").range("C16").value=[CountIfs('Central Data'!A:A,COUNTIFS('Central Data'!M:M,A23),"On Hold")]
    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.

  4. #4
    Thank you SamT and p45cal for responding to my thread. Most appreciated.

    I've tried the code and get a #VALUE! error returned.
    I've tried modifying the "On Hold" value to reference a range cell instead, and still have the same issue.

    Again, appreciate any support i can get from the group.

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    3 steps

    1. Write the first formula in a spare cell referencing A23. Test it
    2. Write the second Formula in another cell referencing the first cell. Test it
    3. When it all works, Substitute the first formula into the second formula where it references the first one.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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