Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to create dynamic reference table name in Excel
  • Get column name from index in Excel Table
  • Conditional Formatting Color Scales Examples in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Reverse List in Excel

References

  • Two-column Lookup in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Create hyperlink with VLOOKUP in Excel

Data Validations

  • Excel Data validation don’t exceed total
  • How To Create Drop-down List in Excel
  • Excel Data validation date in specific year
  • Excel Data validation must contain specific text
  • Excel Data validation require unique number

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”.

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”.

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.

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

Post navigation

Previous Post:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon 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

Logical Functions

  • Extract multiple matches into separate rows in Excel
  • How to use Excel TRUE Function
  • How to use IFS function in Excel
  • IF with wildcards in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert date to month and year in Excel
  • Get week number from date in Excel
  • DAY function: Description, Usage, Syntax, Examples and Explanation
  • Count dates in current month in Excel
  • How to calculate next anniversary date or birthday in Excel

Grouping

  • Group arbitrary text values in Excel
  • If cell contains one of many things in Excel
  • How to randomly assign data to groups in Excel
  • Map inputs to arbitrary values in Excel
  • Group numbers with VLOOKUP in Excel

General

  • List worksheet index numbers in Excel
  • Count cells less than in Excel
  • How to generate random date between two dates in Excel
  • How to get original price from percentage discount in Excel
  • Check if range contains a value not in another range in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning