-
How to find specific text within a string, in a COUNTIFS statement
Hi everyone, I am using a COUNTIFS to search a linked sheet, with one of the conditional IFS to be if the date falls in a specific month. So Condition 1, Condition 2, Condition 3 = Must have "Jan" in referenced dates Have tried "*"&"jan"&"*" as both an IF, and as part of a nested FIND statement - both to no success. Any…
-
Trying to use AverageIF.
Hello I am trying to make a formula that counts the average amount of time an active (open) issue takes, whilst segregating it by priority (low, medium, high). To count the total amount I used this formula: =SUMIFS({Time to fix}; {Solved}; 0; {Priority}; "LOW") However I want to make a new formula that counts the "time to…
-
Making parent complete if children complete BY REFERENCING OTHER COLUMNS
Hi. I know similar questions have been asked, but none seem to reference other columns, which is the key problem for me. Problem: In my sheet, the Health column symbol changes color based on End Date column UNLESS the Progress column is marked "Complete", in which case health turns green. However, as you can see in the…
-
Countifs with an IF statement and dates
Hi, I have two dates in my sheet - a final filing date and an extended final filing date - I am trying to identify the number of advertisements that are closed (for clean ads)- I can count with this countifs statement the FFD but don't know how to factor in the extended final filing date when that field contains a date…
-
Help with multiple IF (CONTAINS) AND OR criteria
Hello! I'm trying to figure out how to write a formula to return a value of "Open" or "Closed" when multiple criteria is met, or leave blank if N/A. Columns: "Issue Category (Consolidated)" column contains selections "Invoice Mgmt", "Inventory", etc. "Status" dropdown column = Not Started, In Progress, Completed,…
-
how?
I am trying to add multiple columns with certain criteria, but I get a 0 and no the actual answer. The formula I am using is =COUNTIFS({1. Tech Sonic All Incident Summary Range 1}, {1. Tech Sonic All Incident Summary Range 2}, {1. Tech Sonic All Incident Summary Range 3}, {1. Tech Sonic All Incident Summary Range 4}, {1.…
-
Populating a Date field using formulas
Hi All, I have 2 known variables that I want to use to populate a Date field: We know the date that a Learner starts a course [Start_Date], and we know the cadence at which a report should be pulled afterward [Report_Cadence] (sometimes in weeks, sometimes in weeks + days). I am having trouble creating a formal that will…
-
Calculating percent of a percent in an =IF(AND formula
I'm trying to write a formula that says if one cell is less than 30% of another cell, then note the status as "At Risk." =IF(AND([Percent Complete]@row < [Timeline Progress]@row, [Percent Complete]@row = [Timeline Progress]@row * 0.3) / 1, "At Risk", "No Value")
-
Use OR in a formula
Hello. I am working on a formula and I have most of it complete. I have never used the OR function, but I think that is what I need to make this work correctly. Maybe it's just me, but I never really understand how to use it from just the Smartsheet examples. Here is my current formula: =COUNTIFS({WO Tickets…
-
Calculating markups for multiple value ranges in a single formula in Excel/Sheets
Hello! I'm a relative newcomer to spreadsheet formulas. I'm trying to use one formula to create standard markups for items based on fixed cost ranges. I thought I figured out how to use greater and less than in a formula and how to nest them together for multiple values and calculations, but instead of getting a markup…