Vlookup Formula to reference sheet of multiple drop down values.

Options
Bnwinter
Bnwinter ✭✭
edited 12/08/22 in Formulas and Functions

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

  • Stacy Meadows
    Stacy Meadows ✭✭✭
    Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!