Sumif
Posted by Linda on September 22, 2000 1:47 PM
I would like to sum a column of numbers if the values in another column are not equal to any of 4 numbers. I can do a sumif with one number but can't get more than one to work.
This worked for one: =SUMIF(A2:A102,"<>127",J2:J102)
How do I add more than one value to discard out of the sum of column J?
Posted by Tim Francis-Wright on September 22, 2000 2:28 PM
You can use an array formula (use control-shift-
enter instead of enter):
=SUM((a2:a102<>31)*(A2:A102<>63)*(A2:A102<>127)*D2:D102)
Each of the a2:a102<> clauses equals 1 if TRUE,
sothe formula only takes a value from D2:D102
if the three clauses are all true.
HTH
