-
Count within last 30 days if a specified value
Hi! Could someone offer me some help with my formula below please? I need to count how many rows have a date within the last 30 days in column 'Timestamp', and have a value above 8 in column 'NPS', and have a certain name in column 'Centre': =COUNTIFS({Feedback mastersheet Range 1}, <=TODAY(30), {Feedback mastersheet Range…
-
IF OR Question
I'm trying to calculate a Total Amount column where if the Type field is either Faculty or EMT then the Total Amount equals Total Meals * 100 (each meal costs $100), but otherwise is $0. I keep getting errors with this: =IF(OR(Type@row, "Faculty", Type@row, "EMT", [Total Meals]@row * 100, 0))
-
Trying to return a value once, but not when cell value changes
Sorry I don't really know how to phrase this. Basically in the projects we have, we get a quote from a vendor, and they automatically enter it into our project sheets. Because this can change to a lower price or cancelled altogether we set up automation so our price column changes to the lower cost, or to zero if…
-
SUMIFS AND CONTAINS
I'm trying to sum the quantity column for all rows where the JO's match and the PN's match. What am I doing wrong, it returns a 0? Here's the formula: =SUMIFS(Quantity:Quantity, JO:JO, AND(CONTAINS(JO@row, @cell)), PN:PN, AND(CONTAINS(PN@row, @cell)))
-
SUM COLLECT Multiple referenced columns
I want to automate data on a metrics sheet from a referenced income and expenditure calculating sheet. On the referenced sheet I have the project names listed under sections which group them into the relevant project type, calculating under the relevant month and totalling in the section Parent row. Each month I delete the…
-
Automate SUM referenced multiple columns
I want to automate a metrics sheet (if possible), to reduce the manual effort of updating each month. I have one sheet that calculates the current month and future project income and expenditure by a formula in each relevant month's column, referencing the project sheet matching the account name and totalling in Parent…
-
populating one cell (selecting from drop-down options) based on content of another cell in sheet
Hi there, first question posted on the Smartsheet Community, here we go... I'm trying to work out a formula/solution for automatically populating a cell (from a drop-down containing 5 geographic location options) based on the first three characters of a postcode (zip code) in another column (approx 150 postcodes). For…
-
Is there a Smartsheet template for Earned Value Management or Analysis?
-
Multi dropdown on report
Hi, I'm trying to do a report which shows things from a multi drop down list; in this case, quarters. The projects can run over multiple quarters and I want them to be showing in each of the quarter. I have created grouping with the quarters too but it won't show the data when in multi dropdown, only when it is a single…
-
formula to automate a return not working (2)
Hi, I'm stuck once again with a slightly different formula, maybe you could help? Following up from the above set of possibilities, the ACTION column should return one of the following options: SELECT RISK SEVERITY - when no option has been previously entered (=default setting) OK TO PROCEED (if risk severity is…