-
Formula to get sheet summary to update percentage complete based on category value
Hello! I am trying to manipulate a formula in my sheet summary to return a percentage complete based on the value selected in the dropdown menu I created. The formula I've tried (and a few variations of it) is: =100 * COUNTIFS([Review Complete]:[Review Complete], 1) / COUNTM([Primary Practice Area]:[Primary Practice Area],…
-
Can someone help me with the IF/THEN formula?
I have a sheet that has a column with a total score. If the score is between the ranges below I want a new column to reflect the label of met, partially met or did not meet. Scoring Ranges and Criteria 301-500 = Met 101-300 = Partially Met less than 100 = Did Not Meet
-
Date Formulas?
How can I write a formula to tell me when a date is past three years from today but does not give a response if there is no date?
-
INDEX/MATCH with multiple "Matches"
I'm designing a project plan template and would like to use an INDEX/MATCH formula to populate a "Business Unit" column based on the staff member assigned to a particular task. Doing this would then allow me to create reports for the different Business Unit Mangers (filtered by the Business Unit column) that allow them to…
-
Receiving #invalid value output from a formula
I have a fairly simple ask and I originally built the formula myself, but since the output was an error I ran it through Smartsheet formula generator and our internal company AI. Both gave the same formula. I have a column with Client Names that can begin with either a letter or number. I have a column that needs to output…
-
VLookup not returning a contact
I am using vlookup to look up a contact from a source sheet and have it insert it as a Contact in another sheet. It is looking up the right person, but is returning a text only of the person's name. I wanted as email contact to in the target sheet to use for a workflow automation. How can I get it to return a contact? The…
-
Formula for counting if cell has RYG symbol
I need to count the total number of cells within a column if they have either Red, Yellow, or Green symbol. We also have the grey symbol, but that should not be counted. I am using this as a way to indicate the total number of active projects in our dashboard. I have tried =COUNTIF(Status:Status, "Green") OR(Status:Status,…
-
Formula stopped working
My formula updates the next date still works but stop working to update the %due column. My formula and screenshot =IF(AND(ISDATE([Last Date]@row), ISNUMBER(Frequency@row)), DATE(YEAR([Last Date]@row) + (FLOOR((MONTH([Last Date]@row) + Frequency@row - 1) / 12, 1)), IF(MOD(MONTH([Last Date]@row) + Frequency@row, 12) = 0,…
-
Collect Counts From Multiple Sheets
Happy Wednesday Smartsheet Community. I have another issue where I am attempting to collect system type counts from three multiple sheets. Unfortunately I have not been able to get the formula to accomplish this. Below is formula using to attempt the compilation In the formula I am asking to pull from the P931 Admin Sheet,…
-
How can I systemically calculate a "Score" based on new field entries for new records?
I need to generate a business value you score for new lead entries, based on three customer profile entries for every new lead entered. HOw can this be done. 1) I tried in the form, but no luck. 2) I tried in automation, but no field calculations are available to be passed to the Smartsheet. (They come through as Text and…