-
Combining formulas
Hi, I need some help with a formula. I have a one check mark column that I would like to 'check' if any of the following formulas are true. Can I combine them into one large formula? =IF(AND([Key Activity]@row, 1, [CEC Tech]@row, 1), 1) =IF(AND([Key Activity]@row, 1, [Corp iT]@row, 1), 1) =IF(AND([Key Activity]@row, 1,…
-
averageif with multiple criteria
I am trying to find the average of time in position (demographics range 1)when the job class (demographics range 3) is manager or higher. I'm currently using =avg(collect to try to specify the multiple criteria but am getting a #divide by zero error message. I have my formula written as =AVG(COLLECT({Demographics Range 1},…
-
Total rows in specific column excluding extra column if checked
All, (Please see screenshot for reference) I am trying to get a "total locations" excluding if "cancelled" is checked. Current formula in "total locations is. =COUNT([Location #]:[Location #]) This gives the total of every row in the "Location #" column, but I need to show the total of what is left if one is marked…
-
Pulling Data from Sheet 1 to Sheet 2 using VLOOKUP
Hello, I have 2019 monthly data in Sheet 1 and would like for it to be populated in Sheet 2 based on current 2020 month. Basically, I want to pull same month last year data from sheet 1 to sheet 2. I've tried using VLOOKUP in sheet 2 cell but the place where I get stuck is "column_num". Column_num should be looking at a…
-
Currency Conversion USD to CAD
Hello I am currently using both currencies in my sheet. I created a column drop down, need help with a formula. If I select USD from that column, the amount should be converted to CAD if I select CAD from that drop down column then the amount should remain same. Thanks
-
IF(INDEX(MATCH Function - For Resource Tracking
Hello, I have an Procurement Engagement Form (PEF) and a Resource Tracker. I'm trying to pull the Contract End Date into the Resource Tracker using the Function below. Its working perfectly in Excel, although unfortunately I'm struggling to get it to work in Smartsheet. =IF({Procurement Engagement Form Range 3} =…
-
I need help with nesting syntax please
I am trying to count the number of tasks assigned to a resource, let's say "Jane". The COUNTIF function works if there is only one resource in the "Assigned To" category, but I also need to include the number of times Jane appears in the "Assigned To" category if there are multiple resources assigned to a task. So I think…
-
Limiting chart limit to a value
Hi, I have created a chart widget in Smartsheet dashboard and I would like the horizontal axis to go up to the value of 100 only and not 120. Is there any way to bring it to 100? Please see attached images of the chart and the data that the chart is using to build the chart.
-
How to count the number of children based on certain value of parent
The column name is [Task] and a cell value called "Project Details" is located somewhere in the column. Their children is project 1, project2, ... etc.... I want to count the number of children under "Project Details". I know that if the position of the cell "Project Details" is fixed. I can use formula like this:…
-
Need assistance with a formula
Hello, I am using the below formula to count the number of rows where 140R was checked previous month. This formula was working perfectly fine until this month when the year changed. How can I fix this formula where previous month should be looked at December 2019 for January 2020, but it should look at January 2020 in…
