COUNTIF does not return the value as I expected

[Guest]

I want to count the range which the date is less than the date at different
Cell (from M2 to M15) in the Sheet 2, but the final data in the Sheet1:
M L
1 Date Julian Time
2 6/8/2005 2005159
3
4
5
6 6/9/2005 2005160
7 6/8/2005 2005159
8 3/15/2005 2005074
9 4/22/2005 2005112
10
11 6/28/2005 2005179
12 6/8/2005 2005159
13 6/8/2005 2005159
14 5/20/2005 2005140
15 4/21/2005 2005111
=COUNTIF('Video Games'!M2:M15,"<6/9/2005") This way does not return data
correctly.
=COUNTIF('Sheet2'!L:L15,"<'2005159") Convert to Julian Time, the count is
correct
I want to change the formula like this for each cell in the Sheet1
=COUNTIF('Sheet2'!L2:L15,">'Sheet2'!L2")
=COUNTIF('Sheet2'!L2:L15,">'Sheet2'!L3â€)
However, the data returns incorrect. Any suggetion will be appreciated.

 

[Guest]

Hi,
Have you tried,
=COUNTIF('Sheet2'!L2:L15,">" & 'Sheet2'!L2)
=COUNTIF('Sheet2'!L2:L15,">" & 'Sheet2'!L3)
you may want to format the value of 'Sheet2'!L2 properly eg: replace
'Sheet2'!L2 with for example TEXT('Sheet2'!L2,"000000")

 

[Bob Phillips]

=COUNTIF('Sheet2'!L2:L15,">"&Sheet2'L2)

 

[Guest]

Hi, the cell shows "#NAME?" if I change it to =COUNTIF('Sheet2'!L2:L15,">" &
'Sheet2'!L3)

 

[Guest]

Hi, Bob,

It complains that "The formula ou type contains an error"...

 

[Dave Peterson]

Bob dropped a single quote and an exclamation point.

=COUNTIF('Sheet2'!L2:L15,">"&'Sheet2'!L2)

But that's the same formula you got a #Name? error.


Any chance you're in R1C1 reference style?

If yes, then copy the formula from the formulabar and clear that cell, then
Tools|options|general tab and uncheck that R1C1 option
then paste in your formula.

 

[Guest]

I must got a typo. The formula works. Appreciate to all.