When we normally use SUMIF or SUMIFS we calculate the sum for values which are fulfilling a criterion. Now we are calculating the sum for exclude condition.

You have a list of values in B column and a letter in C. You need to count the sum for all the other letters but not a, b, and c. We know which values need to be left out.

If the value in C column is something else than a or b or c, then you should count the sum in column B.

This can be done in several ways.

The IF sentence in in D3 is =IF(NOT(OR(C3=”a”;C3=”b”;C3=”c”));”X”;””).

If C3 is not a, b, or c, then mark X, if C3 value is a, b or c, then leave the value blank.

Then the SUMIF is counting the values with X.

If you remove NOT formula and change X from if_value_true to if_value_false, you will get the same result.

I just use NOT formula very seldom, so I wanted to test NOT here.

An advantage with this solution is that it is easy to visualize but the disadvantage is that you need to add one new column.

Another way is to calculate with SUMIF what is the sum for a + sum for b + sum for c. Then the total is diminished by three SUMIF formulas.

The sentence is =SUM($B$3:$B$12)-(SUMIF($C$3:$C$12;”a”;$B$3:$B$12)+SUMIF($C$3:$C$12;”b”;$B$3:$B$12)+SUMIF($C$3:$C$12;”c”;$B$3:$B$12)) .

Now we don’t need to add an extra column but the sentence is quite long.

One solution is to use SUMIFS function. SUMIFS holds several criteria, when SUMIF operates with just one criterion.

The sentence is:

=SUMIFS(B3:B12;C3:C12;”<>a”;C3:C12;”<>b”;C3:C12;”<>c”) .

First, we define the sum range B3:B12. Then we have criteria range for the first criteria, that is C3:C12. The first criterion is that value should not be a, that is written “<>a”. The second criterion has the value “<>b” and the third “<>c”.

For me, the first example with an additional column is the clearest way to calculate the excluding sum if. That means creating an additional column, but I still prefer that way.