-
Counting cells with certain criteria and within the last year
I have to count how many cells contain a certain value, and have occurred in the last year. Example: Count of all closed projects within the last year Project 1 Closed 1/2/23 Project 2 Closed 1/2/21 Project 2 Open 1/2/24 What is the right formula? In excel you can use and count if ( >=date() - 365) or something like that.…
-
Formula for date when a box is checked
I am trying to write a formula that will enter the date when a checkbox is checked. I have an automation set up but it needs to be updated every month so I thought I could write a simple formula to enter the date (approval date) when the box is checked by the approver
-
Counting specific values in multi select cells
Hi All, I am trying to count how many offices have certain engagements (choices are Calls, Email, Weekly PF call, FB gorup, Other, Texts). These cells are linked cells from another sheet. I tried these formulas: =countif([Office Engagement:Office Engagement], contains("Calls", Office Engagement@row)) =countif([Office…
-
Row Report, Count Distinct and Summary Formula?
I have a row report that I am building off of a sheet that includes the date, name, and time spent on a task. I am wanting to build a row report grouped by name, that summarizes the sum of the time spent on a task and the count of distinct dates. The part that I am struggling with is counting the distinct dates, as I only…
-
How would you combine the CONTAINS or HAS function with a SUMIFS formula?
I am trying to create a formula which sums the total number of machines we have delivered for certain projects, but in the "Project Name" column, we have different stages of the same project which I want to combine. E.g. Project Pearl 1.1 - 5 machines delivered Project Pearl 1.2 - 5 machines delivered Project 1.3 - 5…
-
Counting number of times Name appears in multiple contact column
I have a column set up as a multiple contact to capture all authors on a journal publication. I would like to use a formula that will count the total number of times a specific name appears in then column. I have tried =COUNTIF({ALL AUTHORS},"John Doe"), but I know it does not yield all of the times the name appears. I…
-
IF and IFCOUNTS to reference across 2 other sheets
Hey community - We are migrating from excel to using Smartsheets. We have a lot of formulas that are being migrated over as well within these sheets. Here's the fomula working very well in excel: Sheet names referenced within the same book in excel are: Ignore Opps Account Assignment_Round 1.Refresh Customer Doc…
-
Index/Match with 2 sheets
Hi all! First, thanks for taking the time to help me, it is greatly appreciated. I am trying to do an INDEX/MATCH since I can't use XLOOKUP (and this is to the left). First, I created a Unique ID with the JOIN feature to help me accomplish this. That formulas is: =IF([Release Name]@row = "", "", JOIN([Release…
-
Datasheet reference URL
I have a column with the link to another datasheet, how can I write the full URL into another column?
-
Formula for only parent rows
I created a helper column to sum all the children rows under a parent row. I want to now create a column formula that only applies to the parent rows, that populates whatever is in the child row below the parent row into the parent row. I have it working if I apply the formula to each parent row but can't get it to work as…
