+ Reply to Thread
Results 1 to 4 of 4

VBA for Countifs with mixed Variable and Greater-than

  1. #1
    Registered User
    Join Date
    03-02-2006
    MS-Off Ver
    2013
    Posts
    60

    Question VBA for Countifs with mixed Variable and Greater-than

    I'm having trouble with the syntax to create a COUNTIFS Less than Variable WkStart, and Greater Than or Equal to Variable WkEnd for criterias 2 and 3.

    I have defined the variables:
    HTML Code: 
        Dim WkStart As Date
        Dim WkEnd As Date
        
        WkStart = "11/4/2019"
        WkEnd = "11/8/2019"
    I have the syntax figured for a CountIFs with a variable:
    HTML Code: 
        'Using a variable
        Sheets("Change Report").Select
        Range("B2").Select
        ActiveCell.FormulaR1C1 = _
            "=COUNTIFS(WorkingData!C[-1],""LP"",WorkingData!C[16],""*Attainment*"",WorkingData!C[8]," & WkEnd & ",WorkingData!C[8]," & WkStart & ")"
    And using the operators <>=:
    HTML Code: 
        'With <>=
        Range("B2").Select
        ActiveCell.FormulaR1C1 = _
            "=COUNTIFS(WorkingData!C[-1],""LP"",WorkingData!C[16],""*Attainment*"",WorkingData!C[8],""<11/8/2019"",WorkingData!C[8],"">=11/4/2019"")"
    But am having no success with mixing them together.

    The result i'm looking for Should look something like: '=COUNTIFS(WorkingData!A:A,"LP",WorkingData!R:R,"*Attainment*",WorkingData!J:J,"<11/8/2019",WorkingData!J:J,">=11/4/2019")

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,822

    Re: VBA for Countifs with mixed Variable and Greater-than

    Have you tried:
    Please Login or Register  to view this content.
    Note that every quotation mark, which shall be visible in formula is represented as doubled quotation mark.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    03-02-2006
    MS-Off Ver
    2013
    Posts
    60

    Re: VBA for Countifs with mixed Variable and Greater-than

    Kaper- Success!

    All these quotes and cell references make me crazy sometimes.

    I just for reference, i took another approach that did work-
    HTML Code: 
    Range("B2") = Application.WorksheetFunction.CountIfs(Worksheets("WorkingData").Range("A:A"), "LP", Worksheets("WorkingData").Range("R:R"), "*Attainment*", Worksheets("WorkingData").Range("J:J"), "<=" & WkEnd, Worksheets("WorkingData").Range("J:J"), ">=" & WkStart)

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,822
    Just one comment - using worksheetfunction you write value in cell, while with formular1c1 - real formula. Depending on your needs one or the second approach could be better suited

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] countifs and greater than
    By pboost1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-31-2018, 12:25 PM
  2. [SOLVED] Countifs dates greater than 0
    By morerockin in forum Excel General
    Replies: 1
    Last Post: 02-14-2017, 08:57 AM
  3. Countifs not working with greater than (>1)
    By toeyb87 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-12-2016, 05:28 PM
  4. [SOLVED] Countifs - Dates greater than.
    By JasonNeedsHelp in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-15-2015, 06:20 PM
  5. [SOLVED] Using greater than or less than today as a criteria in a countifs
    By santanicopandimonium in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-05-2014, 02:48 AM
  6. Help With COUNTIFS Using Greater Than Criteria
    By amerain in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-04-2013, 11:31 AM
  7. Excel 2007 : Help with Mixed and Variable width Charts
    By Excel_Monkey in forum Excel General
    Replies: 1
    Last Post: 02-21-2012, 08:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1