-
Can someone help me count tasks coming due (using both original & revised due date columns)?
Hello, fellow Community members! I'm new to formulas/equations in smartsheet and could use your help. I'm trying to create a formula that counts how many actions are due in the next 7 days. The tricky bit for me is that there's both an "Actions Planned Due Date (Original)" column and a "Actions Planned Due Date (Revised)"…
-
How to use a formula for SUMIFS with more than 2 not contains criteria?
If I have 3 columns one is "department", the second is "description", and the third is "amount". I would like to sum up the total "amount" for only department-1 in the "department" column, that doesn't contains "ABC" or "EFG" or "JKL" in the description column. I am trying to use =SUMIFS({amount},…
-
Using INDEX/MATCH, but not returning a blank value
Here's what I'm trying to figure out, and hopefully you can assist. I have a form that adds a new row each time it's completed. Not every field is required though. Using a second sheet, and the INDEX / MATCH functions, I have a sheet that shows the most recent entry for each site. But, when a field on the form (and thus…
-
How do I exclude values from a COUNTIFS expression?
Hello All, I am hoping someone can help me figure out - how do I get my formula to exclude specific status items from its count? I am pretty new to formulas - I would like my formula to count the number of statuses by type - and exclude any that are 'Cancelled' or 'Complete' Here is what I have so far - I tried various…
-
Subtraction Issue when there is no input
Hello, I have two separate formulae to collect data based off of dates and input, I need a further formula to subtract one from the other, but I am struggling to get it to work. Subtracting them works fine, Cell v Cell, until there is no data input. I thought perhaps ISNUMBER might be a strong way to go but hit an…
-
If checkbox is checked, add certain fields to a destination sheet
Hi there! I am hoping for a formula/solution for a cross sheet formula. If a checked box is checked on the source file, I want to add info in 8 columns to a destination sheet. I have two software request forms (and I need to have 2 forms) however 8 columns have duplicate information. Instead of having users file out the…
-
Can we use JOIN with an IF Statement
Hi The scenario is I run a meeting with tasks for an agenda item and we have a list of them. Each task has a reference to the Agenda Item. eg. Agenda Item 1 - Action 1 - update plan Agenda Item 1 - Action 2 - distribute plan Agenda Item 2 - Action 1 - create resourcing requirements Agenda Item 2 - Action 2 - provide…
-
Multi-select Dropdown - Select Starting Number characters From Each Option
Hello, I am trying to create a helper column that pulls data from one of my Multi-select Dropdown columns. The data I want the helper column to collect is the number portion of each option selected in the Multi-select Dropdown. I've looked a several posts showing how to pull text strings from multi-select options, but it…
-
Only count items that aren't marked "complete" in the status column.
Hello All, I am hoping someone might be able to help me w/ my formula? I'd like it to only count items that aren't marked "complete" in the status column. This is what I have so far: =COUNTIFS({Remediations Category}, "NEC VIOLATION - ARC FLASH FAILURE", {Remediations Core OPCO}, "NSP", {Remediations Year}, "2022",…
-
AVG(COLLECT with Multiple Criteria
Hi, I am trying to calculate the Average for the "Days Open" column using multiple criteria. Currently I am using two different formulas (below) to try and come up with one number. It would be great to use one formula instead of two. Your help is much appreciated! =AVG(COLLECT({Master Case List Days Open}, {Open/Closed},…
