Thread: Used Sumif and <> now need to add another <> and can not figure out

I will post the time sheet on here, the formula works great but of course they throw more codes in I have a <> 1, need to add <>2 as well and make the codes in column one keep their format as 0001 instead of 1. Here is the spread sheet.

=IF(A2<>1,G2,80-SUMPRODUCT(($A$2:$A$572<>1)*($B$2:$B$572=$B2),$G$2:$G$572)) is the formula and works great, but I can not get the other <>, I tried and but must have done something wrong cause it did not work. Thanks

Hi Patsyhixon,

To change the format of 1 to 001 in column A, just custom format the cell as 000. To do this, press Ctrl +1 on the cell and go to custom and add format as 000. Now copy -paste this format to all cells in column A.

To add <>2 into the criteria, I did not found 2 in the column A, would suggest you to post the correct worksheet with samples. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Patsyhixon,

I found out 2s as well later..
You can use following formula to achieve the desired results:-

=IF(AND(A2<>1,A2<>2),G2,80-SUMPRODUCT(($A$2:$A$572<>1)*($B$2:$B$572=$B2),$G$2:$G$572))]

Sample file is also attached

Regards,
DILIPandey

<click on below 'star' if this helps>

You are welcome patsyhixon..

and thanks for the 'star' .. cheers

Regards,
DILIPandey

<click on below 'star' if this helps>