-
AVG cells with cell references that contain formulas and numbers
Hello- I am trying to avg a group of cells that reference formulas in other sheets and some that only have numbers entered manually. Any advice on what I should do? see screen shot- thank you!
-
Trying to use SUMIFS function to count spend in 2020 by Firm
Struggling newbie here! For the life of me, I can not figure this out. I am trying to find the spend of each firm, and qualify it by the year so that once 2021 rolls around, I can track spend-by-year. I know the end of my formula below is wrong, but I cannot figure out how to qualify by formula by year. I've mastered the…
-
Need help with a formula that is using data from 2 columns
I have a column called Building Turnover and Scheduled - Days Out. Schedule days out takes turnover - today to get a number. I want to see the value and if the value is <30, I want to add 15, if it is I want to add 30. I want all of that in another column Can you help? =IF(OR([Scheduled - Days Out]@row1< 30, [Building…
-
Sumif formula error
I am getting the #INCORRECT ARGUMENT SET error on my formula and can't figure out why. I am assuming it has something to do with the range, but not sure. Below is the current formula I have @Paul Newcome =SUMIF({Program Months Active}, =Date@row, {Monthly Cost}) What I am trying to do is search in the range "Program Months…
-
How to list unique values under a parent row, and summarize in the same cell
Is it possible to list the unique values listed under a parent cell, and give a sum of the values, all in the same cell? When I set my sheet up, I though the "Funded Work Items" would stay the same each month, so right now my sum in the blue, calls the funded work items in the month of October. Here's a sample of it:…
-
At Risk formula
Hello Community, I'm new to Smartsheet and was trying to use the below 'At Risk' formula given from the "Project Rollup & Tracking" Smartsheet template (image #2). I put the formula in my own created project plan, but when I went to my dashboard, the At Risk Tasks ONLY showed itemed that had no date associated with the…
-
Required vs. Completed formula help
I think this is an easy one but my brain is not firing correctly today. I need a formula which will compare how many tasks in the "Required Milestones: OT" have actually been completed. So I have my formula for the required tasks =COUNTIF([Required_Milestones]:[Required_Milestones], "OT") but how do I get it to look at the…
-
Using JOIN / COLLECT with contingent values in multiple columns
I am referencing a sheet with a list of materials and tests performed on the materials. Materials are constantly being added to the list. Each material may need one of 12 tests performed. Once a material is entered in the [Material] column, the sheet populates the 12 possible test columns with "Yes, Perform Test" or "No…
-
Use JOIN/COLLECT to return the first x number of characters
I am using JOIN/COLLECT to return a list of items matching a criteria is a separate sheet. Items are returned in a single cell. The formula I'm using is: =JOIN(COLLECT({Item Number}, {Date_Complete_Helper}, ="No", {ExtTestHelper}, ="No"), SUBSTITUTE(Lots39, "-", "")) (the substitute is used to provide a line break between…
-
End of Year Weeknumber Issues
Hi, I'm having issues with many of my weeknumber formulas, now that it's the end of the year. I have a Start Date column. I want the weeknumber of three weeks after the start date. Until now I used =WEEKNUMBER([Start date]@row) + 3. That doesn't work for the end of the year, because after 53 it goes to 54, 55, 56 ect..…
