Using Name Range in COUNTIF()

[toliphint]

Excel 2007. Relatively new to VBA and don't understand something. A workbook with Excel formulas is using data in another open workbook. Named ranges identifying this data were created in VBA. The named ranges work in these Excel formulas: AVERAGEIF(), MIN(), MAX(), COUNT() and COUNTA(), but do not work in COUNTIF(). An example named range is 5 rows x 1 column below.

Have tried creating the range names in two ways:

Dim LogRng As Range
Dim RangeName As String

RangeName = "RngRef"

'Method 1
Set LogRng = Workbooks(stLogFile_NO_PATH).Worksheets(stLogSheetName).Range("C4:C8")
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=LogRng

'Method 2
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=Workbooks(stLogFile_NO_PATH).Worksheets(stLogSheetName).Range("C4:C8")


Both methods correctly return the named range RngRef which is visible in Name Manager. What am I missing, and how can I return a named range for use in COUNTIF(), and likely COUNTIFS() as well?

PS ---
Don't want to use array formulas as have determined they slow things down. Making tens of thousands of calculations.

 

[CSmith]

Did you read this?

 

[toliphint]

Did you read this?

Yes I did read it, but my question does not apply to the points raised in that post.
- My range is contiguous
- My range name does not have a "dot" in it
- I tried an array CSE formula and does not work
- I'm not trying to add 2 counts

Just trying to understand why the returned range name does not work in COUNTIF() but does work in other functions.
Anyone please?

 

[CSmith]

Are you trying for relative range? or absolute? How are you attempting to use them within a formula?

 

[CSmith]

Here it works for me just fine:

With Named Range: RngRef ='MrExcel Help - toliphint'!$C$4:$C$8

Book1
	C	D	E
4	1		0
5	4		1
6	8		1
7	7		0
8	2		1
MrExcel Help - toliphint

Cell Formulas
Range	Formula
E4	E4	=COUNTIFS(RngRef,"="&(2*INT(RngRef/2)))

 

[toliphint]

Yes it does! And apologies for this post. I cannot explain why, but last night I exited Excel and reloaded and it worked as it should.

 

[CSmith]

Good to hear!

Yes it does! And apologies for this post. I cannot explain why, but last night I exited Excel and reloaded and it worked as it should.