-
Adding up only the numbers that meet a specific criteria in another column
As stated in the titled above, I am trying to figure out how to do a formula that adds certain numbers together in a column, based on if the text in the column next to it, on the same row, is a certain value. Example - Add Column B's Hours together (.5 +3 +6) but only if the column next to it in the same row says "…
-
Using LEFT Function with Countif
Hello, I receive a data from other parties that is formatted inconsistently. The column of interest is labeled Target Months and ranges from things like "36 Mo.", "12 mo" and "1Mo" etc. I am only interested in the number since I know it is referring to months. Using the LEFT function I can strip out the 36 and 12, however…
-
Auto File Number
I have a web form and every time it submits, a new row is added to the top of the sheet. A file number would appear as follows: 20ExC-001 The formula I use is: =IF(Department1 = "Privacy", "20ExC-001", " ") My question is, how can i ensure that every time the form is submitted the last three digits increase by 1, such as…
-
Last 7 days
I am trying to create a formula that will pull data from two columns in the sheet as follows: Sheet columns include intakes by location and a date entered column. I want to create a roll up at the top to show intakes by location in the last 7 days that automatically updates. Here is what I have:…
-
Need help refining a formula where my outcome keeps coming out to 0
For one of my formulas, I am using three columns; a date column (Week Begin) , name column, and number column (total inbound). I am trying to sum certain values in the total inbound column based on the name associated with the data and date range (one week). I am using the formula: =SUMIFS([Total Inbound]:[Total Inbound],…
-
RYG Milestone AND All Other Tasks
Hello - I have a team project plan that consists of 1) milestone tasks, and 2) all other tasks. There is a column ("Milestone') that is a checkbox that denotes if it is a milestone task if checked. At the bottom of the Smartsheet, I want to have a milestone summary that reports the following: Milestone task - If at least,…
-
Help automate the field update in formula so that my summary is always showing the most recent data
I am currently doing some simple data entry in Smartsheet for my company. I am fairly new to Smartsheet and I am still working on my skills in Excel. For one of my sheet summaries, I am using the formula =SUMIF(Date:Date, ="07/14/20", [COLUMN NAME]:[COLUMN NAME]) to sum the data in [COLUMN NAME] based on the current date.…
-
Interactive Dashboard
Hey all, Currently our organization has 56 Primary Care clinics in the DFW area. We are trying to create a central location where all the information associated with the clinic can be stored and updated. Examples are who is the Regional manager, how many physicians are at the office etc. I made a spreadsheet in Grid view…
-
Help | IF/AND Formula for Automating RYG Based on Due Date and Whether Task is Complete
Hello - I have a project plan that I am trying to populate R/Y/G status that uses two criteria: 1) is the task marked completed, and, 2) due date. If the task is due by one day or more AND is not complete, = red If the task is due today and is within 5 business days AND not complete, = yellow If the task is marked complete…
-
If nesting... in a true/false - FOUND IT! Remove the 0 in the nesting
I can't figure out why combing these statements isn't working. This works: =IF(AND(CONTAINS("Director", [Approvals Needed]@row), CONTAINS("Lead or Supervisor", [Approvals Needed]@row), CONTAINS("Manager", [Approvals Needed]@row), [Lead or Supervisor Approval]@row = "Approved", [Manager Approval]@row = "Approved"), 1, 0)…