Formula Help!

Options

I'm creating a dashboard that contains some metrics. I have my metric worksheet already setup and have pulled over info. I'm needing a particular formula and cannot seem to figure it out. Here is what I'm working with:

I need to search one column of information for any input values for only the past two weeks IF a different column contains a certain name.

Column A: Company Names

Column B: Dates

Column C: # values from 1-10

To visualize I need to pull the # values from column C for the last two week (column B) only if column A contains a particular company name.

Is this possible? I've been searching and searching for over a week now and have tried so many different formulas. Thanks for any and all help here!

Tags:

• Options

Mandy,

In my opinion, the best way to address your situation is not through a formula, but by using a report. Here is what I did:

• Created a sheet with your columns and some bogus data
• Created a report to pull the data per your conditions (as seen below)

It appears that it is returning the correct results to me.﻿ Let me know if this is what you were looking for or if you have questions.

Steve

• Options

Thanks, Steve! It sounds like a great plan, but when I went to do it apparently I'm not an admin..? Which doesn't make much sense to me since I'm the one that created the reports..

Is there are formula that would say IF(company name = A) AND (date = today-14) THEN (Zone = 4, 7) or whatever zone numbers are applicable per the input data.

• ✭✭✭✭✭✭
Options

Try a JOIN/COLLECT like so...

=JOIN(COLLECT([Zone Column]:[Zone Column], [Company Name]:[Company Name], "Company A", [Date Column]:[Date Column], AND(@cell <> "", @cell >= TODAY(-14)))

• Options

I'm getting an #unparseable error. =/

• ✭✭✭✭✭✭
Options

Can you provide the EXACT formula you have in your sheet along with the exact names of each column to be referenced?

• Options

Sorry for the delay here! I was waiting on some additional info.

The columns that I am working with are:

Start Date of Activity (will need to be within the last two weeks from current date), Venue Location( will need to select "Newark EWR-TA"), System Type (will need to select "iDAS"), Zone (trying to pull whatever zone numbers are in the cell(s) after running the other criteria)

=JOIN(CONTAINS(1_2_3_14, {Updated Copy_DAS Work Log Range 3}, and(IF({Updated Copy_DAS Work Log Range 1}, "Newark EWR-TA", and(IF({Updated Copy_DAS Work Log Range 5}, <=TODAY(), {Updated Copy_DAS Work Log Range 5}, >=TODAY(-14), ({Updated Copy_DAS Work Log Range 3}:{Updated Copy_DAS Work Log Range 3}, ", "))))))

• Options

ok thank you! I think I'm on the right track now.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!