Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > COUNTIF does not return the value as I expected
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ciccia
 
Posts: n/a
Default COUNTIF does not return the value as I expected

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.

Reply With QuoteReply With Quote
  #2   Report Post  
sebastienm
 
Posts: n/a
Default
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")

--
Regards,
SĆ©bastien


"ciccia" wrote:


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.

Reply With QuoteReply With Quote
  #3   Report Post  
ciccia
 
Posts: n/a
Default
Hi, the cell shows "#NAME?" if I change it to =COUNTIF('Sheet2'!L2:L15,"" &
'Sheet2'!L3)



"sebastienm" wrote:

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")

--
Regards,
SĆ©bastien


"ciccia" wrote:


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.

Reply With QuoteReply With Quote
  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default
=COUNTIF('Sheet2'!L2:L15,""&Sheet2'L2)


--
HTH

Bob Phillips

"ciccia" wrote in message
...

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.



Reply With QuoteReply With Quote
  #5   Report Post  
ciccia
 
Posts: n/a
Default
Hi, Bob,

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

"Bob Phillips" wrote:

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


--
HTH

Bob Phillips

"ciccia" wrote in message
...

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.




Reply With QuoteReply With Quote
  #6   Report Post  
Dave Peterson
 
Posts: n/a
Default
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.





ciccia wrote:

Hi, Bob,

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

"Bob Phillips" wrote:

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


--
HTH

Bob Phillips

"ciccia" wrote in message
...

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.





--

Dave Peterson
Reply With QuoteReply With Quote
  #7   Report Post  
ciccia
 
Posts: n/a
Default
I must got a typo. The formula works. Appreciate to all.

"Dave Peterson" wrote:

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.





ciccia wrote:

Hi, Bob,

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

"Bob Phillips" wrote:

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


--
HTH

Bob Phillips

"ciccia" wrote in message
...

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.





--

Dave Peterson

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Huge problem with "if" formula's [email protected] Excel Discussion (Misc queries) 16 July 20th 05 07:20 PM
Excel - return a picture or range rows as the result of a formula juststarting Excel Worksheet Functions 1 July 4th 05 12:59 AM
How do create a formula to evalute a # to return 1 of 4 conditions Larry Excel Worksheet Functions 4 May 29th 05 12:58 AM
if the value of a cell in a range is not blank, then return the v. kvail Excel Worksheet Functions 2 April 8th 05 10:07 PM
Countif - Countif maswinney Excel Worksheet Functions 3 November 15th 04 11:06 PM


All times are GMT +1. The time now is 07:22 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement