Quick Summing Question

  • Thread starter Thread starter bimmerman
  • Start date Start date
B

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
 
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)
 
Back
Top