Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    Jan 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a variable inside a formula

    Hello I have a repeating entries in several formula's that I would like to setup as a variable. The formula below is one such example I use frequently throughout a series of worksheets. This one in particular is for the first quarter.

    =COUNTIFS(Data14!$C:$C, "<=3/31/2014", Data14!$C:$C, ">=1/1/2014", Data14!$H:$H, A2)

    I have setup a variable called, "Quarter1" which contains - Data14!$C:$C, "<=3/31/2014", Data14!$C:$C, ">=1/1/2014" - and when I write the formula

    =COUNTIFS(Quarter1, Data14!$H;$H, A2) it does not work.

    I am thinking it has to do with quotes but my searches and attempted have not lead me to an answer.

    Thanks for your help

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MB,

    Welcome to the Lounge as a new poster!

    I'm assuming that you are trying to do this directly in a worksheet cell vs using VBA.

    With that assumption in place I don't think you can do it in that exact way. But you can do it this way.
    variables.JPG
    Range Names: QtrStart = E1, QtrEnd = F1
    I used SumIFs in the example what CountIFs will work just the same.
    Note: I also used dates one outside of the quarter on both sides so I could simplify the formula to > < vs >= <=, just a personal preference. YMMV

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MB,

    Welcome to the Lounge as a new poster!

    I'm assuming that you are trying to do this directly in a worksheet cell vs using VBA.

    With that assumption in place I don't think you can do it in that exact way. But you can do it this way.
    =SUMIFS(B2:B47,A2:A47,">"&QtrStart,A2:A47,"<"&QtrE nd) <--Note no space just the software!
    variables.JPG
    Range Names: QtrStart = E1, QtrEnd = F1
    I used SumIFs in the example what CountIFs will work just the same.
    Note: I also used dates one outside of the quarter on both sides so I could simplify the formula to > < vs >= <=, just a personal preference. YMMV

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi RG

    ..I'm thinking you are giving SUMS when MB is asking for COUNTS.
    But still useful.

    ..this is how I did it:
    1. on a spare sheet, format 2 cells as text.
    2. name the cells from and to respectively
    3. In the from cell, enter >=1/1/14
    4. In the to cell, enter <=3/31/14 (for UK, enter as <=31/3/14 i.e. as dd/yy/mm)
    5. on sheet [Data14], select entire column A (click the frame on A), and in the namebox assign the name colA
    6. click on column [C] and enter the name colC
    7. click on col [H] and enter the name colH

    your formula now becomes:
    =COUNTIFS(colC,from,colC,to,colH,A2)

    zeddy

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Nice variation!

    ..I'm thinking you are giving SUMS when MB is asking for COUNTS.
    But still useful.
    I mentioned that in the text. But the concept works the same SumIFS or CountIFs.

    In the end both methods work, your method gives a cleaner looking formula while mine makes for easier data input as the user doesn't have to worry about the logic signs just the dates. Pick your poison.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2015-01-27)

  7. #6
    New Lounger
    Join Date
    Jan 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the welcome RG and thanks to you both for the quick reply. I will try both and see what works best.

    mb

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi RG

    Oops! Yes, you mentioned that in your text. Trouble is, when I see an excel formula I sometimes forget to look at the rest.

    As punishment, I have used your method for entering dates into cells, then created named cells to and from with formulas referring to these date cells. See attached file.
    And as a further punishment, I added a right-click menu to display a date-picker (with a shortcut keystroke [Ctrl][Shift][C], and as even further punishment, I made sure that the calendar was displayed on the correct monitor when using multi-screens with Excel.

    If anyone needs to know how to get the calendar control, it was mentioned in this lounge on 2013-09-13.

    zeddy
    Attached Files Attached Files

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    You are absolved! ROTFLOL.gif

    Nice work on the enhancements!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    MBHighes,

    You could also use a User Defined Function (UDF) for simplicity. Enter the formula:

    =CountQtr("H",A2) in the cell where you want the calculated result.

    This formula will have the equivalent of:

    =COUNTIFS(Data14!$C:$C,"<=3/31/2014",Data14!$C:$C,">=1/1/2014",Data14!$H:$H,A2)

    The first two criteria's and their ranges will be a constant which is what I think you were trying to achieve. To use the formula, in the UDF change the Column "H" (with the quotes) and Criteria cell A2 to whatever you like. For example: =CountQtr("J",A4)

    In the images, Cell E3 has your full formula whereas Cell E4 has the UDF. Note that they both return the same value.

    E3: =COUNTIFS(Data14!$C:$C,"<=3/31/2014",Data14!$C:$C,">=1/1/2014",Data14!$H:$H,A2)
    CountQtr1.png

    E4: =CountQtr("H",A2)
    CountQtr2.pngCountQtr2.png

    Place in a standard module:
    Code:
    Public Function CountQtr(col As String, Criteria As Range) As Long
    Dim CriteriaRng As Range, Rng As Range
    Set CriteriaRng = Worksheets("Data14").Columns("C")
    Set Rng = Worksheets("Data14").Columns(col)
    CountQtr = WorksheetFunction.CountIfs(CriteriaRng, "<=3/31/2014", CriteriaRng, ">=1/1/2014", Rng, Criteria)
    End Function
    Once this is placed in a standard module, the CountQtr formula becomes available anywhere in the workbook. I have supplied different versions in case you were using 2003.

    HTH,
    Maud
    Attached Files Attached Files

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    To build on Maud's fine work let's take the UDF a step further and allow calculation for any quarter as follows:

    =CountQtr("H",A2,1)

    Code:
    ublic Function CountQtr(col As String, Criteria As Range, iQtr As Integer) As Long
    
        Dim CriteriaRng As Range
        Dim Rng         As Range
        Dim Qtrs(1 To 4, 1 To 2) As Date
        
        Qtrs(1, 1) = DateValue("1/1/2014")
        Qtrs(1, 2) = DateValue("3/31/2014")
        Qtrs(2, 1) = DateValue("4/1/2014")
        Qtrs(2, 2) = DateValue("6/30/2014")
        Qtrs(3, 1) = DateValue("7/1/2014")
        Qtrs(3, 2) = DateValue("9/30/2014")
        Qtrs(4, 1) = DateValue("10/1/2014")
        Qtrs(4, 2) = DateValue("12/31/2014")
        
        Set CriteriaRng = Worksheets("Data14").Columns("C")
        Set Rng = Worksheets("Data14").Columns(col)
        CountQtr = WorksheetFunction.CountIfs(CriteriaRng, "<=" & Qtrs(iQtr, 2), _
                   CriteriaRng, ">=" & Qtrs(iQtr, 1), Rng, Criteria)
                   
    End Function   'CountQtr
    Of course we could continue and add error checking for the parameters, e.g. iQtr < 1 or > 4, etc.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    RG,

    Yes, that does make it more versatile. Nicely done! My other thought was to use optional parameters in the function to allow the entry of either a range or an actual date for the criteria.

    Maud

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Maud

    ..but it doesn't work in the UK as we use dd/mm/yyyy over here.


    zeddy

  14. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zeddy,

    Just following the date format that the OP used. You may need to adjust the date formatting in the code for your region.

    Maud

  15. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    You can change the DateValue function calls to Date function calls then it will work everywhere, e.g.

    DateValue("1,1,2014") becomes Date(2014,1,1), etc.

    HTH

    Update: Drat! VBA WorksheetFunction doesn't support Date() back to the drawing board!
    Last edited by RetiredGeek; 2015-01-28 at 09:04.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    According to my research this change should make it work anywhere. Would you be so kind as to give it a go.
    Code:
    Public Function CountQtr(col As String, Criteria As Range, iQtr As Integer) As Long
    
        Dim CriteriaRng As Range
        Dim Rng         As Range
        Dim Qtrs(1 To 4, 1 To 2) As Date
            
        Qtrs(1, 1) = #1/1/2014#
        Qtrs(1, 2) = #3/31/2014#
        Qtrs(2, 1) = #4/1/2014#
        Qtrs(2, 2) = #6/30/2014#
        Qtrs(3, 1) = #7/1/2014#
        Qtrs(3, 2) = #9/30/2014#
        Qtrs(4, 1) = #10/1/2014#
        Qtrs(4, 2) = #12/31/2014#
        
        Set CriteriaRng = Worksheets("Data14").Columns("C")
        Set Rng = Worksheets("Data14").Columns(col)
        CountQtr = WorksheetFunction.CountIfs(CriteriaRng, "<=" & Qtrs(iQtr, 2), _
                   CriteriaRng, ">=" & Qtrs(iQtr, 1), Rng, Criteria)
                   
    End Function   'CountQtr
    HTH
    Last edited by RetiredGeek; 2015-01-28 at 09:23.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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