How to reference two columns on a different sheet for a formula on another sheet using countif
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
Best Answer
-
To many parenthesis. Try this...
=COUNTIFS({Studio Network Stack Submissions Range 2}, "not validated",{Studio Network Stack Submissions 6-5-2020 Range 1}, "Pilot")
Answers
-
To many parenthesis. Try this...
=COUNTIFS({Studio Network Stack Submissions Range 2}, "not validated",{Studio Network Stack Submissions 6-5-2020 Range 1}, "Pilot")
-
Hello Paul,
Thank you so much! So simple!
You ROCK!
-
Happy to help! 👍️
-
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")
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
No luck, Its still only referencing one column. :(
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Please, explain again what you want the formula to do.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!