Formula result incorrect

[Andrew Chalk]

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.

 

[JMB]

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

 

[Ragdyer]

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

 

[Andrew Chalk]

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]

Yes, it appears I did leave that out. 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))

 

[William Benson]

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.

 

[JMB]

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