CSE formula results

S

sa02000

I am getting same results from
=COUNT(IF(T4:Z4>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
 
R

RagDyeR

Try this:

=COUNTIF(T4:Z4,">200")

Use regular <Enter>,
*Not* CSE.
--

HTH,

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

message
I am getting same results from
=COUNT(IF(T4:Z4>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
 
V

vezerid

Jay,
Your IF returns 0 if the condition is not true. Therefore COUNT()
counts them, being numbers.
Your solution is
=COUNTIF(T4:Z4, ">200").

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

=SUM(IF(T4:Z4>200,1,0))

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

HTH
Kostis Vezerides
 
T

Tom Ogilvy

Ragyar 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 >200. So the count function counts the zero
cells as well as the number cells.

In the formula bar, select
IF(T4:Z4>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:Z4>200,T4:Z4))

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

sa02000

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

Jay
 
Top