Forum Discussion
SUMIF (or other function) if the cell value in either of 2 columns is >0
I want to sum the cells in column C if the row value in column A is >0 OR if the row value in column B is >0 (not AND).
With SUMIF, I tried setting the search range to both columns A and B, but the result was very odd; I haven't yet figured out how SUMIF arrived at is sum.
SUMIFS only works as AND, nor OR, to my knowledge.
Thank you for your help.
3 Replies
Hi MLF1912,
For orientation see colors of cells in the screenshot above.
Use this formula in an empty cell:
=SUM(BYROW(A2:B11 > 0, OR) * C2:C11)or this formula:
=SUM((BYROW(--(A2:B11 > 0), SUM) = 1) * C2:C11)To ensure the formulas work correctly, you will need to use one of the following versions of Excel:
- Excel for Microsoft 365
- Excel for Microsoft 365 for Mac
- Excel for the web
In other versions of Excel, these formulas do not work.
If none of the solutions above match what you’re looking for, please explain your issue in more detail by providing an example and indicating what the total should be in your specific case. Also, please let us know which version of Excel you are using.
Hope this helps.
IlirU
Sum When Condition Is OR
- SUMIF and SUMIFS cover only the AND condition, as you have correctly concluded.
- SUMIF and SUMIFS support only ranges, not arrays.
- Use SUMPRODUCT instead of SUM in legacy versions of Excel (<=2019) to avoid confirming with
Ctrl+Shift+Enter.
.
=SUM((((A2:A11>0)+(B2:B11>0))>0)*C2:C11) =SUMPRODUCT((((A2:A11>0)+(B2:B11>0))>0)*C2:C11)Hi
in E1:
=SUM( IF( ( A1:A10 > 0 ) + ( B1:B10 > 0 ), C1:C10 ) )And validate with [Ctrl]+[Shift]+[Enter] if you run Excel < 2021
