Help with SUMIF or array function to ignore #VALUE!
 

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

Help with SUMIF or array function to ignore #VALUE!
 

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.

--
Biff
Microsoft Excel MVP


"Nelly" wrote in message
. uk...
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

Help with SUMIF or array function to ignore #VALUE!
 

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
---
"Nelly" wrote in message
. uk...
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))}