software

I am getting same results from
=COUNT(IF(T4:Z4gt;200,T4:Z4,0))
(this formula is used as an array formula using Control shift enter but
curley brackets are not displayed when I copy and paste here)
as well as
=COUNT(T4:Z4)
I get formula result as 4, when only 2 of my target cells have value
greater than 200.
Here is what my target cells look like
296.79118.45217.121.10#N/A#N/A#N/A

These numbers in target cells are results of a vlookup (thats why
#N/A). So I tried using an AND inside IF in first formula with second
argument of AND being ISNUMBER (to eliminate any potential problem from
#N/A) but I still get the same result ..that is 4.
Does anybody have any clue why this might be?

Thanks, Jay--
sa02000
------------------------------------------------------------------------
sa02000's Profile: www.excelforum.com/member.php...oamp;userid=27747
View this thread: www.excelforum.com/showthread...hreadid=508925Try this:

=COUNTIF(T4:Z4,quot;gt;200quot;)

Use regular lt;Entergt;,
*Not* CSE.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

quot;sa02000quot; gt; wrote in
message ...

I am getting same results from
=COUNT(IF(T4:Z4gt;200,T4:Z4,0))
(this formula is used as an array formula using Control shift enter but
curley brackets are not displayed when I copy and paste here)
as well as
=COUNT(T4:Z4)
I get formula result as 4, when only 2 of my target cells have value
greater than 200.
Here is what my target cells look like
296.79 118.45 217.12 1.10 #N/A #N/A #N/A

These numbers in target cells are results of a vlookup (thats why
#N/A). So I tried using an AND inside IF in first formula with second
argument of AND being ISNUMBER (to eliminate any potential problem from
#N/A) but I still get the same result ..that is 4.
Does anybody have any clue why this might be?

Thanks, Jay--
sa02000
------------------------------------------------------------------------
sa02000's Profile:
www.excelforum.com/member.php...oamp;userid=27747
View this thread: www.excelforum.com/showthread...hreadid=508925
Jay,
Your IF returns 0 if the condition is not true. Therefore COUNT()
counts them, being numbers.
Your solution is
=COUNTIF(T4:Z4, quot;gt;200quot;).

If you want to proceed into more elaborate conditional counting you
could use SUMPRODUCT() or, with array formulas something like:

=SUM(IF(T4:Z4gt;200,1,0))

The idea being that we add 1 to each entry meeting the condition.

HTH
Kostis VezeridesRagyar gave you a good solution. The answer to your problem is that any
cells not containing an error value return either the value in that cell or
zero depending on if they are gt;200. So the count function counts the zero
cells as well as the number cells.

In the formula bar, select
IF(T4:Z4gt;200,T4:Z4,0) and hit F9.

Look at what is being returned. No hit the escape key.

To fix your formula change it to
=COUNT(IF(T4:Z4gt;200,T4:Z4))

so false will be displayed for cells not greater than 200 and not containing
an error.

--
Regards,
Tom Ogilvy

quot;sa02000quot; gt; wrote in
message ...
gt;
gt; I am getting same results from
gt; =COUNT(IF(T4:Z4gt;200,T4:Z4,0))
gt; (this formula is used as an array formula using Control shift enter but
gt; curley brackets are not displayed when I copy and paste here)
gt; as well as
gt; =COUNT(T4:Z4)
gt; I get formula result as 4, when only 2 of my target cells have value
gt; greater than 200.
gt; Here is what my target cells look like
gt; 296.79 118.45 217.12 1.10 #N/A #N/A #N/A
gt;
gt; These numbers in target cells are results of a vlookup (thats why
gt; #N/A). So I tried using an AND inside IF in first formula with second
gt; argument of AND being ISNUMBER (to eliminate any potential problem from
gt; #N/A) but I still get the same result ..that is 4.
gt; Does anybody have any clue why this might be?
gt;
gt; Thanks, Jay
gt;
gt;
gt; --
gt; sa02000
gt; ------------------------------------------------------------------------
gt; sa02000's Profile:
www.excelforum.com/member.php...oamp;userid=27747
gt; View this thread: www.excelforum.com/showthread...hreadid=508925
gt;

I used COUNTIF formula and it worked great. Thank you all for your
replies.

Jay--
sa02000
------------------------------------------------------------------------
sa02000's Profile: www.excelforum.com/member.php...oamp;userid=27747
View this thread: www.excelforum.com/showthread...hreadid=508925