Table of Contents
SUMIF is a useful function in Microsoft Excel that allows users to quickly calculate the sum of a specific range of cells based on a single criteria. However, in some cases, multiple criteria may need to be applied. This is where the OR function comes in. By combining SUMIF with OR, users can specify multiple criteria within a single formula, allowing them to perform a more flexible and accurate calculation. This can be particularly useful when dealing with large datasets or when trying to find specific values within a range of data. Overall, using SUMIF with OR in Excel enables users to efficiently and effectively perform complex calculations, making data analysis and management easier and more efficient.
Use SUMIF with OR in Excel
You can use the following formulas to combine the SUMIF function with the OR function in Excel:
Method 1: SUMIF with OR (One Column)
=SUM(SUMIFS(B2:B13, A2:A13,{"Value1","Value2", "Value3"}))
This particular formula finds the sum of values in B2:B13 where the corresponding value in A2:A13 contains “Value1”, “Value2”, or “Value3.”
Method 2: SUMIF with OR (Multiple Columns)
=SUMIF(A2:A13,"Value1", C2:C13)+SUMIF(B2:B13,"Value2", C2:C13)
This particular formula finds the sum of values in C2:C13 where the corresponding value in A2:A13 contains “Value1” or the corresponding value in B2:B13 contains “Value2.”
The following example shows how to use each method in practice with the following dataset in Excel:
Example 1: SUMIF with OR (One Column)
We can use the following formula to sum the values in the Points column where the value in the Team column is equal to “Mavs” or “Rockets”:
=SUM(SUMIFS(C2:C13, A2:A13,{"Mavs","Rockets"}))The following screenshot shows how to use this formula in practice:
The sum of the values in the Points column where the value in the Team column is equal to “Mavs” or “Rockets” is 53.
Example 2: SUMIF with OR (Multiple Columns)
We can use the following formula to sum the values in the Points column where the value in the Team column is equal to “Mavs” or the value in the Position column is “Center”:
=SUMIF(A2:A13,"Mavs",C2:C13)+SUMIF(B2:B13,"Center",C2:C13)The following screenshot shows how to use this formula in practice:
The sum of the values in the Points column where the value in the Team column is equal to “Mavs” or the value in the Position column is “Center” is 87.
Additional Resources
The following tutorials explain how to perform other common SUMIF operations in Excel:
Cite this article
stats writer (2024). How can I use SUMIF with OR in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-sumif-with-or-in-excel/
stats writer. "How can I use SUMIF with OR in Excel?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-sumif-with-or-in-excel/.
stats writer. "How can I use SUMIF with OR in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-sumif-with-or-in-excel/.
stats writer (2024) 'How can I use SUMIF with OR in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-sumif-with-or-in-excel/.
[1] stats writer, "How can I use SUMIF with OR in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use SUMIF with OR in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.