Vlookup Formula to reference sheet of multiple drop down values.
In each sheet I've created, I also created a workflow that will copy the days tasks for my team into another sheet. I then created a "metrics" sheet to count how many times a particular team has tasks assigned to it for that day.
I'm having issues with this formula and was hoping to have some help in the reference section.
=COUNTIF({Daily Tasks Range 8}, CONTAINS(VLOOKUP([Primary Column]@row, [Responsible]@column, 10, false) @cell))
That is the current formula I'm trying to use.
That is where the formula lives.
It is trying to reference this sheet.
I need the responsible column to be referenced here and counted in the "metric" sheet in the first image.
My idea for this came from this page: https://community.smartsheet.com/discussion/81470/count-how-many-times-a-multi-select-value-was-selected-in-a-cell-range-or-column#latest
Best Answer
-
Do not think that will work as it needs to be the same type of column which it is not as you cannot do that on a primary column...
I would suggest
Account =COUNTIFS({Responsible}, FIND("Account", @cell) > 0)
PM =COUNTIFS({Responsible}, FIND("PM", @cell) > 0)
Client =COUNTIFS({Responsible}, FIND("Client",@cell) > 0)
the {{Responsible}} range just needs to be the responsible column and so forth if I have understood correctly :)
Answers
-
Do not think that will work as it needs to be the same type of column which it is not as you cannot do that on a primary column...
I would suggest
Account =COUNTIFS({Responsible}, FIND("Account", @cell) > 0)
PM =COUNTIFS({Responsible}, FIND("PM", @cell) > 0)
Client =COUNTIFS({Responsible}, FIND("Client",@cell) > 0)
the {{Responsible}} range just needs to be the responsible column and so forth if I have understood correctly :)
-
So I actually have a lot more than you can see there. About 30 total that can be selected, and sometimes multiple per column. Is there no way to have it select what is possible given the information in that primary column?
And just for reference here, the sheet name being reference in the first image is "Daily Tasks". I know that has to be worked into the formula as the reference sheet.
The formulas above work, just would be a pain to add that in for each of the 30 that I have. But if I only do it once, it isn't too bad haha.
-
Nevermind! Got it. Just referenced the cell to the left in the formula and pulled it down!
-
Can you send us that formula :)
-
=COUNTIFS({Daily Tasks Range 8}, FIND(Team@row, @cell) > 0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
