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