-
Need to calculate advance per week with a range of two dates.
Dear all, I need to support to determine a formula that allows me To perform a weekly count of how many days (or if If any) will happen this week based on range of between 2 dates. Example Today is 3/19/2020 Start Date End Date 3/16/2020 04/02/20 This week will have a count = to 5 days from within that range. (3/16 - 3/20)…
-
Date updating to Next Day using Index, Match, Max, Collect Formula
Hi, I'm working with two Smartsheets. (1) One sheet collects the data from a Smartsheet form. I added two columns to this sheet using the Auto-Number/System Feature (under Edit Column Properties). This features stamps new row entries with a date/time (Created) and by whom (Created By). I'm curious about the cell surrounded…
-
Verify All Sites Submitted Response Today
Hi, I'm trying to track if a site (or Name) created a new daily entry using a Smartsheet form. I added this formula to the Updated Today column to return a "Yes" answer if the site (or Name) created a new daily entry. This seems to be working as intended. Is there a better way to design this formula? =IF(Created@row =…
-
VLOOKUP plus a Dropdown selection???
I don't think this is possible, but thought I'd ask here. I have a situation where I would like to look up a value in another worksheet if the the value in a column adjacent to the lookup contains "DMP". I know how to write that formula, but if the value does not contain DMP, then I don't want the lookup at all, but want…
-
Sum if checkbox is not checked?
What is the formula if I want to sum only the numbers that don't have a box checked in their row? At the bottom of this screenshot, the $9,000 should return a sum of $6500.
-
SUM and SUMIF Formula
Can anyone help me. I have multiple offices located in different regions (Central, Northwest, Northeast). I need to get the sum of patients seen in each region and place into a dashboard. Example: Central has 4 offices;1 office saw 5 patient,s 1 office saw 0 patients, 1 office saw 25 patients, 2 office saw 6 patients. I…
-
SUMIFS
I am trying to create a SUMIFS formula that will allow me to pull data from the following columns: Region: APAC (There are several offices that make up the APAC region) OfficeStatus: Closed OfficeHeadCount: Sum Total (For example 8 APAC Offices so want the sum of all their headcount in closed offices) I have this formula…
-
Different types of zeros?
The formula below is creating multiple "zeros". See pictures. What am I missing? =IF(Balance@row = "ERROR", "ERROR", IF(Balance@row = "TBD", "TBD", IF(Balance@row = "DEDUCT", "DEDUCT", ((SUM(Jan@row:Dec@row) + SUM(CHILDREN(Jan@row:Dec@row))) - (((SUM(CHILDREN([Contract Amount]@row), "Change") + ([Contract Amount]@row)) -…
-
CountIF but not blanks
Hi everyone - sorry if this has already been asked and answered. But, i am trying to have a box checked if there is a duplicate entry. This is the formula i am using: =IF(COUNTIF([FA Peoplesoft ID]:[FA Peoplesoft ID], [FA Peoplesoft ID]@row) > 1, 1, 0) It works, but when i add the formula to the duplicate column all the…
-
Incorrect counting of blank rows
="Completed " + COUNTIF([Completed Tasks]:[Completed Tasks], 1) + " of " + COUNT([Completed Tasks]:[Completed Tasks], IF([Task Name]:[Task Name], "")) I have a checkbox column that will read in the summary bar (row 1) when working "Completed 6 of 33" However, it is counting blank rows and should read "Completed 6 of 25"…