In this Article

*This tutorial will demonstrate how to use the SUMIFS Function to sum rows not equal to specific values in Excel and Google Sheets.*

## Sum If Not Equal To

The SUMIFS Function sums data that meet certain criteria. Its syntax is:

This example will sum the **Revenue** for all **Order Numbers** not equal to 527.

1 |
=SUMIFS(C3:C9,B3:B9,"<>527") |

As shown above, to test whether the **Order Number** is not equal to 527, we use:

1 |
"<>527" |

Note that when hard-coding the criteria into the SUMIFS Function, the logical test must be within double quotes (” “).

Other logical operators can also be used in this formula, such as:

- Equal to (“=524”)
- Greater than (“>526”)
- Greater than or equal to (“>=529”)
- Less than (“<528”)
- Less than or equal to (“<=525”)

## Sum If Not Equal To – Cell References

Usually, it is bad practice to hard-code values into formulas. Instead, it is more flexible to use a separate cell to define the criteria’s value.

1 |
=SUMIFS(C3:C9,B3:B9,"<>"&E3) |

Start with the logical operator within double quotes (“<>”) and use the & symbol to join the operator with the cell reference:

1 |
"<>"&E3 |

## Locking Cell References

To make our formulas easier to read, we’ve shown the formulas without locked cell references:

1 |
=SUMIFS(C3:C9,B3:B9,"<>"&E3) |

But these formulas will not work properly when copy and pasted elsewhere in your file. Instead, you should use locked cell references like this:

1 |
=SUMIFS($C$3:$C$9,$B$3:$B$9,"<>"&E3) |

Read our article on Locking Cell References to learn more.

## Sum If Not Equal To in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.