-
VLOOKUP resulting in some references coming back as #NO MATCH.
I have a master Smartsheet people fill-out a form for that submits purchase orders made. I have a corresponding Smartsheet that people fill-out a form for to indicate when a specific PO has been received. In the master Smartsheet I am attempting to use a VLOOKUP that looks at the PO number from the master Smartsheet and…
-
COUNTIFS Formula Issue
Hi all, I'm trying to count how many times a particular field appears in a range across two different sheets, but I keep getting an #UNPARSEABLE and I can't figure out why. My formula is below: =COUNTIFS({2021 Completed Sim Equipment}, "24 Week Airway Trainer",[{In Progress Sim Equipment}, "24 Week Airway Trainer"]) If I…
-
SUMIFS
Hi, I'm looking to sum the cells in a column if another corresponding column is either of two values. I'm getting #UNPARSEABLE though! 😕 =SUMIFS({Data Range Spend}, {Data Range Award Type}, OR(@cell = "Direct Award”, @cell = "Mini-Competition”)) Any thoughts welcome! Thanks, Derek
-
Summing FTE Hours available in the future
I am currently utilizing the current formula =SUMIF({Resource name}, [Resource Name]@row, {Weekly Scheduled Hours}) to determine how many hours a resource has available. Since I began using this, some assignments have ended, but resource utilization is still being summed, resulting in weekly hours scheduled being >40. How…
-
Using IF with greater than or equal to a number
I want the "Complete" column to show "yes" if the total reviews is greater than or equal to 3. I cannot figure out what I did wrong with the formula above. Thanks!
-
Nested ISBLANK Formula
Hi I want to be able to look up changes to e.g. employee name, Job Title etc in a sheet and update the any changes on a staff sheet. However, if for example, their job title hasn't changed I need the formula to refer to 'old job title' column. I have a unique identifier of the Employee ID so, wondered if an INDEX MATCH…
-
Using HAS with Multi-Selection Dropdown
Hi, I have a multi-selection dropdown with region selections. Next to that column I have the regions. For reporting purposes I need the column for each region to say "Yes" or "No" based on the items selected in the multi-select dropdown. I've created this formula with a variation for each column: =IF(HAS("Central",…
-
Ignore blanks while detecting matching text across multiple columns
Hello! I am trying to write a formula for detecting ANY matches between "Affiliation" and "Affiliation 1," "Affiliation 2," or "Affiliation 3". If any of them match "Affiliation", then "conflict" is true. If false, it should say "none". However, if "Affiliation" is blank and matches another blank, it also outputs…
-
Summing values for a specific date from a date range
Hi all, I have a project sheet with Start and End dates for tasks, as well as a Percent of Team's Time column that tells me how much of my team's capacity will be used on that task. I want to sum up how much of my team's capacity will be used on any given day, since there are multiple tasks with overlapping date ranges…
-
Need Formula help
Hello, I need a formula in order to calculate the sum of specific hours for a resource on a particular week with a certain criteria. For example, I need the formula that calculates the hours for Jane Doe for week ending 4/2/21 charged only to Department A. I tried something with SUMIF or IF(SUMIFS but couldn't get it to…