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.

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.
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> > >
> > >
> > >
>
>

I already answered this I think in a thread titled:
"Re: 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>> >
>> >
>> >
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> > >
> > >
> > >
>
>
>