Can OR function be used with cross-sheet references?

Ty Hickman

I'm trying to use OR with SUMIFS in a cross-sheet calculation but it doesn't work. As an example the formula below yields a INVALID OPERATION error.

=SUMIFS({TotalCost}, {Cost Center}, [Cost Center]@row, OR({Reason} = "Other", {Reason} = "Training"), {Region}, "Domestic", {Approved}, 1, {Year}, "2020")

Thanks,

TY

Genevieve P.

Hi Ty,

Yes, OR can be used within a SUMIFS formula, however OR is a bit different than the way SUMIFS looks at the ranges. With the way you have it set up, you are looking to see if the entire range = one specific criteria (versus looking to see if a specific cell = a criteria).

We will need to make it more specific, looking @cell in each of the ranges instead of looking at the range as a whole. One way to do this would be with the HAS function built in (keep in mind HAS looks for an exact match). Like this:

{Reason}, OR(HAS(@cell, "Training"), HAS(@cell, "Other")

Try this:

=SUMIFS({TotalCost}, {Cost Center}, [Cost Center]@row, {Region}, "Domestic", {Approved}, 1, {Year}, "2020", {Reason}, OR(HAS(@cell, "Training"), HAS(@cell, "Other")))

Let me know if this works for you!

Genevieve