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

Find more posts tagged with

Accepted answers

Stacy Meadows

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 :)

All comments

Stacy Meadows

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 :)

Bnwinter

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.

Bnwinter

Nevermind! Got it. Just referenced the cell to the left in the formula and pulled it down!

Stacy Meadows

https://community.smartsheet.com/discussion/comment/353720#Comment_353720

Can you send us that formula :)

Bnwinter

=COUNTIFS({Daily Tasks Range 8}, FIND(Team@row, @cell) > 0)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.