Quick Summing Question

[bimmerman]

I have this formula here which works as designed:

=SUM(IF(DEFECTS!$A$2:$A$499=A20,IF(DEFECTS!$M$2:$M$499=$H$3,IF(DEFECTS!$I$2:$I$499>$E$1,DEFECTS!$Q$2:$Q$499,0),0),0))


Now I need to convert this formula where it is trying to match $H$
right now to return everything that DOES NOT match $H$3.

Thanks

 

[Frank Kabel]

Hi
using your formula why not simply use:
=SUM(IF(DEFECTS!$A$2:$A$499=A20,IF(DEFECTS!$M$2:$M$499<>$H$3,IF(DEFECTS
!$I$2:$I$499>$E$1,DEFECTS!$Q$2:$Q$499,0),0),0))


Though I would use a (non-array entered) SUMPRODUCT formula for this.
e.g:
=SUMPRODUCT(--(DEFECTS!$A$2:$A$499=A20),--(DEFECTS!$M$2:$M$499<>$H$3),-
-(DEFECTS!$I$2:$I$499>$E$1),DEFECTS!$Q$2:$Q$499)