Sum Values that Meet 1 of Multiple Conditions in Excel

Add to Favorites
Author:

How to sum values that equal one of many potential criteria; this is basically summing with an OR condition.

This is not the same as using the SUMIFS() function because that function only sums values that meet every condition within it.

Formula

=SUMIF(A1:A5,"red",B1:B5)+SUMIF(A1:A5,"black",B1:B5)

This formula sums the numbers for the colors red and black.

The trick is that it is actually two SUMIF functions that are added together within the cell. It's the same as placing one SUMIF function for the red values in one cell and another function in another cell for the black values and then adding the results together.

There is a plus sign in-between both functions and that's what allows them to be in the same cell and added together.

(I'm assuming that you're already familiar with how the SUMIF function works.)

0f9e043c603de85e0d2638631f158863.png

Result:

a51239f3f180c0f07252a986f59de2d7.png

You can continue to add as many SUMIF functions to this formula as you want, basically.

In fact, you can even use this method to subtract, divide, or multiply values based on criteria; simply replace the plus sign with one of the other signs.

Notes

If you want to sum values based on multiple criteria across multiple columns using this sort of OR condition, check out our tutorial on how to sum values that equal 1 of many conditions across multiple columns.

Make sure to download the sample file for this tutorial to work with the above example in Excel.


Excel Function: SUMIF()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Sum Values that Equal 1 of Many Conditions across Multiple Columns in Excel
Tutorial: How to Sum values using an OR condition across multiple columns, including using OR with ...
SUMIFS - Sum Values Based on Multiple Criteria in Excel
Tutorial: The SUMIFS function allows you to sum values that meet multiple criteria across multiple ...
Convert Numbers into True and False in Excel
Tutorial: How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very impor...
Count the Number of Unique Values in a List in Excel
Tutorial: Use a formula to count the number of unique values that are contained within a list in Exc...
Copy Data or Formatting to Multiple Worksheets in Excel
Tutorial: Quickly copy all or parts of a single worksheet - data, formatting, or both - to multiple...
How to use Vlookup Across Multiple Worksheets in Excel
Tutorial: This tutorial shows you how to use the Vlookup function across multiple worksheets within ...
Tutorial Details
Excel Function: SUMIF()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course