Excel SUMIF Formula Not Matching Criteria

In a previous post I talked about SUMIF – summing cells based on a condition, in this post I will talk about SUMIF based on not matching a criteria. So normally you would SUMIF a range based on matching a specific criteria.  But you can also Sum a range based on NOT matching a specific criteria.

Normally, you would type, for example:

So here we would go down the range A1:A10 and anywhere that Test appears then sum the corresponding value in the second referenced range,B1:B10.

But if you wanted to SUMIF and not match a criteria you would type the following:

This formula will go down the first range, and anywhere that Test does NOT appear it will then sum the corresponding value in the second range, B1:B10.

So to use this formula, replace the first range with the one you want, then replace Test with whatever your criteria may be. Then finally, change the second range which would have values to the one that you want.

Leave a Reply

Your email address will not be published. Required fields are marked *