Sum Values that Meet 1 of Multiple Conditions in Excel

Add to Favorites

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

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...
Center Titles Across Multiple Cells in Excel
Tutorial: How to center a title across multiple cells in Excel in order to make good looking titles...
How to use Vlookup Across Multiple Worksheets in Excel
Tutorial: This tutorial shows you how to use the Vlookup function across multiple worksheets within ...