Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel LOOKUP Function
  • How to use Excel COLUMN Function
  • How to reference named range different sheet in Excel
  • How to get last column number in range in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Analysis Examples

  • Create Scatter Chart in Excel
  • Excel Frequency Function Example
  • How To Insert and Customize Sparklines in Excel
  • Get column index in Excel Table
  • Conditional Formatting New Rule with Formulas in Excel

Data Validation Examples

  • Data validation must not exist in list
  • Excel Data validation specific characters only
  • Excel Data validation whole percentage only
  • Excel Data validation unique values only
  • Excel Data validation allow uppercase only

Sum if cells are not equal to in Excel

by

This tutorial shows how to Sum if cells are not equal to in Excel using the example below;

Formula

=SUMIF(range,"<>value",sum_range)

Explanation

To sum cells when other cells are not equal to a specific value, you can use the SUMIF function.

In the example shown, cell H7 contains this formula:

=SUMIF(region,"<>West",amount)

This formula sums the amounts in column E only when the region in column C is not “West”.

Worked Example:   Sum if between in Excel

How the formula works

The SUMIF function supports all of the standard Excel operators, including not-equal-to, which is input as <>.

When you use an operator in the criteria for a function like SUMIF, you need to enclose it in double quotes (“”). In this case, the criteria is input as “<>West” which you can read as “not equal to West”, or simply “not West”.

Worked Example:   Sum lookup values using SUMIF in Excel

Alternative with SUMIFS

You can also use the SUMIFS function to sum if cells are NOT blank. SUMIFS can handle multiple criteria, and the order of the arguments is different from SUMIF. The equivalent SUMIFS formula is:

=SUMIFS(amount, region,"<>West")

Notice that the sum range always comes first in the SUMIFS function.

Worked Example:   Sum if not blank in Excel

SUMIFS allows you to easily extend the criteria to handle more than one condition if needed.

Post navigation

Previous Post:

Sum if date is between in Excel

Next Post:

Sum if cells contain an asterisk in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to check if cell contains number in Excel
  • ASC function: Description, Usage, Syntax, Examples and Explanation
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • PROPER function: Description, Usage, Syntax, Examples and Explanation
  • How to display conditional message with REPT function in Excel
  • Calculate expiration date in Excel
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Get month name from date in Excel
  • Pad week numbers with zeros in Excel
  • How to calculate next anniversary date or birthday in Excel
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annuity for interest rate in excel
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • NPER function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYS function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles