Help with SUMIF or array function to ignore #VALUE!

[Nelly]

Hi all

I need to count data that sometimes includes #VALUE! and cannot work it out

This is what I am using but now and again the data includes #VALUE and due
to tha amount of lookups I cannot ommit this from the data that is been
counted

{=SUM(($M$1:$M$357=$B400)*($F$1:$F$357>=0.02))}


any help much apprieciated

Nelly

 

[T. Valko]

Try this array formula** :

=COUNT(IF(F1:F357>=0.02,IF(M1:M357=B400,1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Max]

Try it like this, array-entered (press CTRL+SHIFT+ENTER to confirm the
formula):
=SUM(IF(ISNUMBER(($M$1:$M$357=$B400)*($F$1:$F$357>=0.02)),($M$1:$M$357=$B400)*($F$1:$F$357>=0.02)))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik