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

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

Ty Hickman

Bingo! It worked. Thanks for the lesson Genevieve. This community is really useful.

Genevieve P.

Wonderful! So glad that worked for you, and happy to help. :)