-
Creating a formula that looks for criteria in a range and searches for that combo in a row
Hello, I hope I explain this the right way. I want to to find if a specific (row-level) combination of employee and date that exists on the first sheet also exists on the second sheet and if so, use a formula to flag the row that has the combination. For example, if Alex and 06/16/20 exists on the first sheet, I want to…
-
SUM IF Name and Points
I need to calculate the total number of points in the "Points Column" by each person in the "Name" column and I cannot figure out how its calculated. I tried to brainstorm with another co-worker and we both had trouble trying to figure this one out. Please advise.
-
vlookup/index/match to place data from one sheet into another
I compile safety data using a form that is filled out by my safety manager. In it he enters the date and if an incident occurs. If no incident occurs it uses a calculation (from my last discussion topic, still having circular reference errors) to sum the total number of days since the last incident. My question, which has…
-
HELP!!!
OK, I need help with a counting formula - This example works in Excel, but not in Smartsheet =IFERROR(COUNTIFSG14:K14, “P”)/COUNTA(G14:K14),””) The goal is to count the number of populated columns in the row only - and get a % in the score column. if blank, no value - 1 (f) = 0% 1(F) and 1(p) = 50% 1 (p) = 100% 2(p) and…
-
Nested IF And Function
I'm trying to create a nested IF Formula to do the following: I want to calculate a capacity score or percentage based on the columns PME (experience rating: easy-expert) and the PCR (complexity rating: easy-expert). My thought a really long nested IF statement where it would go through the following permutations: For…
-
How do I calculate IF statement to multidrop down list?
I can trying to get my sheet to calculate different values based on what is selected in the drop down. Example: If "Partner introduction" is selected populate 10 points in "points earned" column and $1 in the "dollar value earned" column, If "Quote Request" is selected populate 50 points in the "points earned" column and…
-
Looking to have a stacked collum chart showing difference in values
Hello, I am looking to have a stacked column chart showing the change in two values over the course of past, present, and future dates. I'd like this graph to update with new dates automatically based on what is added in the tracker. The stacked column chart should display the months of a year in the x axis and thee…
-
JOIN COLLECT two variables
I am trying to find a way to collect two different variables from two different ranges {courseyear} and {regstatus}, then join them together to determine the year and registration status in a single cell: Here is what I would like: =JOIN(COLLECT({courseyear}, {id}, $ID@row, ({regstatus}, {id}, $ID@row)), "-") Output:…
-
Need help with 'If , Then' function to subtract Duration Values
I'm setting up a Construction Schedule with Variance calculations as the 'Near Term' baseline solution that's been quoted as coming for a few years now has not materialized yet. Unfortunately after searching the forums I'm not seeing syntax examples that work for what I need to do. It would be helpful for me at least to…
-
Roll up of assessment data with conditions
I need to be able to roll up associated transmittal #s to 1 Transmittal and if the assessment status is Not Compliant for any of the Compliance Assessments then Transmittal 1 should equal Not Compliant, Transmittal 2 = Not Compliant, Transmittal 3= Compliant