Formula result incorrect
 

I'm a beginner to using Excel formulae and have run across a puzzling
problem. Why does my attempt to COUNT the number of entries in column e that
are equal to 5098 when the date is 5/27 give me an incorrect result? I get
"1" but can see that there are at least 2.

Here is a subset of the data:

a b c d e
DATE TIME TRUNK ANI DNIS
5/27/2005 7:32:12 121 5414
5/27/2005 7:32:14 122 5098
5/27/2005 7:32:17 123 5098



Here is the formula:

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))

Many thanks.

Formula result incorrect
 

Maybe double check your data types. If data in column A is not text, use
datevalue to convert "5/27/05" to a number (you should read excel help
regarding dates - they are actually stored as numbers). Also, if column E is
numeric, remove the quotes. If col E is text, leave the quotes.



=COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500))


"Andrew Chalk" wrote:

I'm a beginner to using Excel formulae and have run across a puzzling
problem. Why does my attempt to COUNT the number of entries in column e that
are equal to 5098 when the date is 5/27 give me an incorrect result? I get
"1" but can see that there are at least 2.

Here is a subset of the data:

a b c d e
DATE TIME TRUNK ANI DNIS
5/27/2005 7:32:12 121 5414
5/27/2005 7:32:14 122 5098
5/27/2005 7:32:17 123 5098



Here is the formula:

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))

Many thanks.

Formula result incorrect
 

The formula that you suggested is an *array* formula.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

A non-array alternative:

=SUMPRODUCT((A2:A500=DATEVALUE("5/27/05"))*(E2:E500=5098))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JMB" wrote in message
...
Maybe double check your data types. If data in column A is not text, use
datevalue to convert "5/27/05" to a number (you should read excel help
regarding dates - they are actually stored as numbers). Also, if column E
is
numeric, remove the quotes. If col E is text, leave the quotes.



=COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500))


"Andrew Chalk" wrote:

I'm a beginner to using Excel formulae and have run across a puzzling
problem. Why does my attempt to COUNT the number of entries in column e
that
are equal to 5098 when the date is 5/27 give me an incorrect result? I
get
"1" but can see that there are at least 2.

Here is a subset of the data:

a b c d e
DATE TIME TRUNK ANI DNIS
5/27/2005 7:32:12 121 5414
5/27/2005 7:32:14 122 5098
5/27/2005 7:32:17 123 5098



Here is the formula:

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))

Many thanks.

Formula result incorrect
 

You hit the nail on the head. The date column was a date, not text. However
it required ragdyers SUMPRODUCT implementation to work correctly.

Thanks,

Andrew
"JMB" wrote in message
...
Maybe double check your data types. If data in column A is not text, use
datevalue to convert "5/27/05" to a number (you should read excel help
regarding dates - they are actually stored as numbers). Also, if column E
is
numeric, remove the quotes. If col E is text, leave the quotes.



=COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500))


"Andrew Chalk" wrote:

I'm a beginner to using Excel formulae and have run across a puzzling
problem. Why does my attempt to COUNT the number of entries in column e
that
are equal to 5098 when the date is 5/27 give me an incorrect result? I
get
"1" but can see that there are at least 2.

Here is a subset of the data:

a b c d e
DATE TIME TRUNK ANI DNIS
5/27/2005 7:32:12 121 5414
5/27/2005 7:32:14 122 5098
5/27/2005 7:32:17 123 5098



Here is the formula:

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))

Many thanks.

Formula result incorrect
 

Yes, it appears I did leave that out. thanks!

"Ragdyer" wrote:


The formula that you suggested is an *array* formula.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

A non-array alternative:

=SUMPRODUCT((A2:A500=DATEVALUE("5/27/05"))*(E2:E500=5098))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JMB" wrote in message
...
Maybe double check your data types. If data in column A is not text, use
datevalue to convert "5/27/05" to a number (you should read excel help
regarding dates - they are actually stored as numbers). Also, if column E
is
numeric, remove the quotes. If col E is text, leave the quotes.



=COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500))


"Andrew Chalk" wrote:

I'm a beginner to using Excel formulae and have run across a puzzling
problem. Why does my attempt to COUNT the number of entries in column e
that
are equal to 5098 when the date is 5/27 give me an incorrect result? I
get
"1" but can see that there are at least 2.

Here is a subset of the data:

a b c d e
DATE TIME TRUNK ANI DNIS
5/27/2005 7:32:12 121 5414
5/27/2005 7:32:14 122 5098
5/27/2005 7:32:17 123 5098



Here is the formula:

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))

Many thanks.

Formula result incorrect
 

I already answered this I think in a thread titled:
" What does this mean?"
I believe the array formula he was entering MAY have resolved to
COUNT(FALSE) which returns 1 no matter what.

Does not change the validity of other suggestions here and pardon me if I am
wrong anyway.

"Ragdyer" wrote in message
...

The formula that you suggested is an *array* formula.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.

A non-array alternative:

=SUMPRODUCT((A2:A500=DATEVALUE("5/27/05"))*(E2:E500=5098))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JMB" wrote in message
...
Maybe double check your data types. If data in column A is not text, use
datevalue to convert "5/27/05" to a number (you should read excel help
regarding dates - they are actually stored as numbers). Also, if column
E
is
numeric, remove the quotes. If col E is text, leave the quotes.



=COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500))


"Andrew Chalk" wrote:

I'm a beginner to using Excel formulae and have run across a puzzling
problem. Why does my attempt to COUNT the number of entries in column e
that
are equal to 5098 when the date is 5/27 give me an incorrect result? I
get
"1" but can see that there are at least 2.

Here is a subset of the data:

a b c d e
DATE TIME TRUNK ANI DNIS
5/27/2005 7:32:12 121 5414
5/27/2005 7:32:14 122 5098
5/27/2005 7:32:17 123 5098



Here is the formula:

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))

Many thanks.

Formula result incorrect
 

as ragdyer pointed out, i omitted the part about the count function being an
array formula, which requires CSE (CNTRL+SHIFT+ENTER).

"Andrew Chalk" wrote:

You hit the nail on the head. The date column was a date, not text. However
it required ragdyers SUMPRODUCT implementation to work correctly.

Thanks,

Andrew
"JMB" wrote in message
...
Maybe double check your data types. If data in column A is not text, use
datevalue to convert "5/27/05" to a number (you should read excel help
regarding dates - they are actually stored as numbers). Also, if column E
is
numeric, remove the quotes. If col E is text, leave the quotes.



=COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500))


"Andrew Chalk" wrote:

I'm a beginner to using Excel formulae and have run across a puzzling
problem. Why does my attempt to COUNT the number of entries in column e
that
are equal to 5098 when the date is 5/27 give me an incorrect result? I
get
"1" but can see that there are at least 2.

Here is a subset of the data:

a b c d e
DATE TIME TRUNK ANI DNIS
5/27/2005 7:32:12 121 5414
5/27/2005 7:32:14 122 5098
5/27/2005 7:32:17 123 5098



Here is the formula:

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))

Many thanks.