# Thread: Using a variable inside a formula

1. ## 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.

2. 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

3. 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

4. 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

=COUNTIFS(colC,from,colC,to,colH,A2)

zeddy

5. 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.

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

zeddy (2015-01-27)

7. 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. 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

9. Zeddy,

You are absolved! ROTFLOL.gif

Nice work on the enhancements!

10. 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

11. 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

12. 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. Hi Maud

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

zeddy

14. 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. 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!

16. 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

Page 1 of 2 12 Last