How to reference two columns on a different sheet for a formula on another sheet using countif

Lauren_C

Hello,

Im creating a rollup page for a dashboard and need to create a formula to count 2 separate columns. Basically i want the amount of "pilot" studios that are "validated" or "not validated.

Here is what i currently have:

=COUNTIFS({Studio Network Stack Submissions Range 2}), "not validated"),{Studio Network Stack Submissions 6-5-2020 Range 1}, "Pilot"))

I keep getting a #UNPARSEABLE error

Paul Newcome

To many parenthesis. Try this...

=COUNTIFS({Studio Network Stack Submissions Range 2}, "not validated",{Studio Network Stack Submissions 6-5-2020 Range 1}, "Pilot")

Paul Newcome

To many parenthesis. Try this...

=COUNTIFS({Studio Network Stack Submissions Range 2}, "not validated",{Studio Network Stack Submissions 6-5-2020 Range 1}, "Pilot")

Lauren_C

Hello Paul,

Thank you so much! So simple!

You ROCK!

Paul Newcome

Happy to help! 👍️

Lauren_C

I have another one for you if youre up for it.

I want the Status Label column to change to "EA Opt-In" when EARLY ADOPTER Y/N column says "Yes"

Something about the below is not working for me :(

=IF([Early Adopter Opt-In?]2:[Early Adopter Opt-In?]410, "yes", [Status Label]1:[Status Label]410, "EA Opt-In")

Andrée Starå

Hi @Lauren_C

Try something like this.

=IF([Status Label]@row = "Yes"; "EA Opt-In")

The same version but with the below changes for convenience.

=IF([Status Label]@row = "Yes", "EA Opt-In")

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Lauren_C

Hello Andree,

That didnt work. I need to to say

If Early Adpter Opt-In? Is "YES". Then the Status Label Column should change to "EA Opt-in"

Your formula isnt wrong, but it assumes that i want only one cell to change. If that makes sense.

Andrée Starå

@Lauren_C

Try this one instead.

=IF([Early Adopter Opt-In?]@row = "Yes"; "EA Opt-In")

The same version but with the below changes for convenience.

=IF([Early Adopter Opt-In?]@row = "Yes", "EA Opt-In")

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Lauren_C

No luck, Its still only referencing one column. :(

Andrée Starå

@Lauren_C

Apologies, I must have read it wrong. I'll read it again.

😉

Andrée Starå

@Lauren_C

Please, explain again what you want the formula to do.

Paul Newcome

@Lauren_C You are going to want to use Andree's formula in every row. You can't use a formula in a single cell to change multiple cells.