SUMIFS with multiple OR criteria??

Kim Stephens

Hi Experts.

I need to do some $$ calculations. I am working on a summary page I need the sum of the column Funding Committed, ignoring the rows where Pre-Approved or Declined are checked (they move the calc to another column). Something like:

=sumifs({Committed:Committed}, {Pre-Approved:PreApproved},1, OR {Declined:Declined}, 1)

I cannot figure out how to make the OR work, and it cannot be an AND. Any help would be appreciated.

Genevieve P.

Hiya!
Happy to help with the alternate formula 🙂 @Kim Stephens

What you can do here is find the SUM for if the checkbox is not checked in one column, then the SUM for if it's not checked in the other column, then subtract the SUM when the row has both un-checked (otherwise we'd have duplicate numbers).

So:

=(SUMIFS(first checkbox) + SUMIFS(second checkbox)) - SUMIFS(both checkboxes in the same row)

Or in your case:

=(SUMIFS({Committed}, {PreApproved}, 0) + SUMIFS({Committed}, {Declined}, 0)) - SUMIFS({Committed}, {PreApproved}, 0, {Declined}, 0)

Let me know if this makes sense and will work for you!

Cheers,
Genevieve

che.rabajante

Hello @Kim Stephens

Please try this:

=SUMIFS({Committed:Committed}, {Pre-Approved:PreApproved}, 0, {Declined:Declined}, 0)

Hope this helps.

che

Kim Stephens

Thanks for looking at this. Does that make the conditions AND or OR. I need them to be OR. They are 2 separate columns, and I need the sum to exclude the row if either of the fields have a checkbox, not just both.

Darla Brown

Hi,

The OR function does not work with Sumif or Sumifs function in Smartsheet. You will need to create a workaround.

There may be others that have an alternative solution for you.

Genevieve P.

Hiya!
Happy to help with the alternate formula 🙂 @Kim Stephens

What you can do here is find the SUM for if the checkbox is not checked in one column, then the SUM for if it's not checked in the other column, then subtract the SUM when the row has both un-checked (otherwise we'd have duplicate numbers).

So:

=(SUMIFS(first checkbox) + SUMIFS(second checkbox)) - SUMIFS(both checkboxes in the same row)

Or in your case:

=(SUMIFS({Committed}, {PreApproved}, 0) + SUMIFS({Committed}, {Declined}, 0)) - SUMIFS({Committed}, {PreApproved}, 0, {Declined}, 0)

Let me know if this makes sense and will work for you!

Cheers,
Genevieve

Genevieve P.

@Kim Stephens

Another alternative is to use a helper formula in the source sheet that indicates if the row meets your criteria or not:

=IF(OR(PreApproved@row = 0, Declined@row = 0), Committed@row, "")

Then you can simply SUM that helper column in your cross-sheet formula.

Darla Brown

Thanks @Genevieve P. I was going to suggest a helper column, but I'm not quite awake enough to write the formula. New someone would come to the rescue! 😀

Paul Newcome

@Darla Brown Just a quick note… The OR function can be used in a SUMIFS, but only when looking at multiple criteria for the same range. It just doesn't work when trying to OR on different ranges.

So in this particular case, the OR wouldn't work.

But if you were (for example) summing everything with a status of "On Hold" or "In Progress", you could use

=SUMIFS({Range to sum}, {Status Column}, OR(@cell = "On Hold", @cell = "In Progress"))

Darla Brown

@Paul Newcome thanks for the reminder!

Paul Newcome

@Darla Brown No worries! 🙂

Kim Stephens

@Darla Brown @Paul Newcome @che.rabajante @Genevieve P.

Thank you all for the answers. I ended up using some helper columns and automation to update the columns so that I do not need to have the OR statement. I appreciate the quick responses.