Issues with CONTAINS using Cross Sheet Reference

RingJake

Hello,

I've created a COUNTIFS formula referencing another sheet. Each section of the COUNTIFS references the same sheet so that isn't the issue I believe. Everything works up until the CONTAINS portion. Is there an error with my syntax?

=COUNTIFS({Status}, New1, {GSP Category}, "New GSP Onboarding", CONTAINS("Onboarding", {Summary}))

Genevieve P.

Hi @RingJake

You'll need to list the range first {Summary}, then identify the criteria you're looking for.

Try this structure instead:

=COUNTIFS({Status}, New1, {GSP Category}, "New GSP Onboarding", {Summary}, CONTAINS("Onboarding", @cell))

Let me know if this works for you!

Cheers,

Genevieve

RingJake

Hey @Genevieve P., worked perfectly. Thank you! Did Summary have to come first because COUNTIFS is referencing it as the range for CONTAINS, then it needed to be referenced again for CONTAINS?

Genevieve P.

Hi @RingJake

Yes, exactly!

The COUNTIFS needs this structure:

{Range}, "Criteria", {Range}, "Criteria"...

So we needed to list the {Summary} as a range first, before identifying what you're looking for within that column.

Now within the "Criteria", you want to see if it contains something... therefore the CONTAINS is specific to the criteria part of the COUNTIFS structure.

It gets tricky because the CONTAINS function also needs a Range listed. In this case though, the Range is each individual cell within the previously stated column, so that's why we use @cell.

Cheers!

Genevieve