Sumif(s) with OR criteria in multiple columns

[CRHAAG]

Hi everyone. I am trying to write a SUMIF() formula that pulls in a total from one column if either one of two other columns is greater than zero.

Value1	Value2	Value3
500	0	1000
0	200	2000
100	100	5000
0	0	4000

So my psuedo code would be:

If value1>0 OR value2>0 SUM Value3

So I would expect the answer in this case to be 8000.

Any insight would be appreciated

 

[Joe4]

If your values2 can never be negative, then you can use this:
=SUMPRODUCT(--(A2:A5+B2:B5>0),--(C2:C5))

 

[CRHAAG]

Joe4 that seems to do the trick. I've never seen that format before. Can you tell me what it is/how it works?

 

[Joe4]

SUMPRODUCT is what we used to use before COUNTIFS and SUMIFS came along (it allows you to do multiple conditions). It can also do some of the more complex one that you cannot easily do with COUNTIF/COUNTIFS or SUMIF/SUMIFS. The double negative signs is just used to coerce the equalities (or inequalities) to numeric values (I actually did not need to use it on the second argument).

Here are some links that show you how to use it:

SUMPRODUCT Function

The Excel SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more flexibility. Other functions can easily be used...

exceljet.net

SUMPRODUCT function - Microsoft Support

How to use the SUMPRODUCT function in Excel, one of Excel’s math and trig functions, using multiplication, addition, subtraction, and/or division to return the sum of the products of corresponding ranges or arrays.

support.office.com

Excel SUMPRODUCT function with multiple criteria - formula examples

Learn the basic and advanced uses of the Excel SUMPRODUCT function – formula examples to compare arrays, conditionally sum or count cells with multiple criteria using AND or OR logic, get a weighted average, and more.

www.ablebits.com