|
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. |
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. |
=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. |
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. |
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. |
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 |
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 |
| All times are GMT +1. The time now is 07:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com