-
Averaging numbers that meet a defined criteria
I'm creating a metrics sheet to feed a dashboard. On this sheet I am referencing a different sheet with the data. The formula I need to create would do the following: 1) AVERAGE the numerical values in one column. 2) IF the data was collected in a certain month, which is identified in a different column. So for example, I…
-
Regrouping Delimited Data
I pull data from an internal system and add it to Smartsheet via Data Uploader. When it comes in, it looks like the above, with Biz Entity, Sub Business Entity and Product Family grouped as delimited data. However, I want to regroup it so that each Biz Entity/Sub Business Entity/Product Family group is together in a single…
-
Help with MAX Function
Hello, I need help writing a MAX formula to return information by the latest creation date. My current formula is =IFERROR(INDEX({CS PT Recruitment Candidate FirstName}, MATCH([Workday PID]@row, {CS PT Recruitment PID}, 0)), ""). If we recruit for a position multiple times in a fiscal year, the position ID remains the same…
-
Can I include a date from another sheet into a formula for my current sheet?
Hello. I'm pretty new to Smartsheet and can't find an answer to today's dilemma. For example, I have a two sheets: ABC Project (belongs to me) and XYZ Project (belongs to our vendor) I want to be able to calculate a start date on one of my tasks in ABC Project by pulling a finish date from XYZ Project? Basically like this:…
-
Count between ranges
Reaching out to see if my formula, which seems to be working, is the correct way to COUNT between two ranges. In my example - I want to count a category name AND only if the % Complete range is between 25% - 49% FORMULA =COUNTIFS({Function}, "Asset Protection", {Percent}, >0.25, {Percent}, <=0.49) As mentioned this seems…
-
Using Contents from one Date cell to update another Date cell
Im trying to use a 'helper column' to run a formula that checks the contents of a date column/cell, and copies those contents to another date column/cell. The code I'm using is quite simple, so I feel that I must be making a silly error, or it is not possible. To re-iterate, I have a cell specific date column , and I want…
-
Formula IF print the date modification of the sheet by the leader.
I want to create an IF formula that will print the date and time of the modification of the sheet only by the leader. It is possible ? Unless there is another formula or method for that ??
-
Use IF/AND or INDEX/MATCH?
I have a sheet (top) where Site Code and Sub-GL are drop downs. There are hundreds of corresponding site/sub-GLs listed in my reference sheet (bottom). I am looking for the correct formula to return value of "2021 OP2 Total" when Site Code and Sub-GLs are selected match a line in the reference sheet. I can't seem to make a…
-
Sumifs with Multi Select DropDowns
Hi all, this is very simple, but I am pulling my hair out! I am trying to do a SumIfs, where one of the criteria is a multi select dropdown. My first attempt is =sumifs({column to be totaled}, {Multi select Column}, Option 1) If I do this, then it only Suns when Option 1 is the only option, not where option 1 and and any…
-
Date updating to Next Day using Index, Match, Max, Collect Formula
Hi, I'm working with two Smartsheets. (1) One sheet collects the data from a Smartsheet form. I added two columns to this sheet using the Auto-Number/System Feature (under Edit Column Properties). This features stamps new row entries with a date/time (Created) and by whom (Created By). I'm curious about the cell surrounded…