Any idea on using OR function with SUMIFS?

Syed Muhafzal

Hi Smarties,

I understand we can use OR function with SUMIFS or COUNTIFS - I just couldn't figure out how. I am wondering if there is a solution to the problem below

• "OR can be used directly in a boolean column (checkbox, flag, star) or can be used within another function—such as IF, SUMIF, COUNTIF, SUMIFS, and COUNTIFS."

Problem Statement: Sum the children rows in the Numbers Column if either the Status is Green OR Completed is checked.

Example sheet below =

I could do SUMIFS, but not sure how to go around having the Status is Green OR Completed is Checked?

=SUMIFS(CHILDREN(Numbers@row), CHILDREN(Status@row), "Green", CHILDREN(Completed@row), 1)

Thanks as always folks!

Jaykel Torres

Hey @Syed Muhafzal,

Although it is possible to use the OR Function within SUMIFS or COUNTIFS Functions, this can only be used when evaluating multiple criteria against the same Column. If more than one Column is involved, you will likely need to create two SUMIFS or COUNTIFS Functions then add them together. I've created the example below to demonstrate this:

Formula (highlighted in yellow): =SUMIFS(Numbers:Numbers, Complete:Complete, 1, Status:Status, @cell <> "Green") + SUMIFS(Numbers:Numbers, Complete:Complete, 0, Status:Status, "Green")

• The first SUMIFS Function sums Numbers where Complete is Checked and the Status is not Green
• The second SUMIFS Function sums Numbers where Complete is Not Checked and the Status is Green.

I hope this helps!

Jaykel