-
Formula - Count by Month and Other Criteria
I've been trying to determine a formula I can use on a SEPERATE Sheet that will pull from the sheet in the screenshot a: - Monthly SUM number of items that are In Review, Awaiting Publication, Published/Live, etc. (i.e., the "Status" column in screenshot) so, for February, number of items in review, awaiting publication,…
-
Inconsistent results from COUNT(DISTINCT(COLLECT
Hello, I am using a separate sheet to calculate unique names (employees/visitors) who have visited the respective locations, separated by morning and afternoon sessions. I was using Sheet Summary in the source sheet but the numbers don't refresh by itself for some reasons, hence I have created a "metrics" sheet. The…
-
Formula using OR
Can someone help me I keep getting a error message, I know is has to be the OR function. =COUNTIFS({Civic 2021 Range 2}, "RFP Submitted", OR({Civic 2021 Range 4}, "(Stage 3) 3.2 - WON", {Civic 2021 Range 4}, "(Stage 3) 3.3 - LOST"), {Civic 2021 Range 3}, MONTH(@cell) = 1)
-
Budget Calculation
I am attempting to calculate the expected spend year to date with a formula that references data in a month column where MONTH(TODAY()) - 1. This works, but when the new year started, it reset all of the expected spend year to date back to 0. Is there away to set a formula that won't =IF(Month6 = "12", SUM([Budget…
-
Date Helper Not Helping
I created a driver check in and the shipper wonder if there was a way that they could see when a line last pick up. So I used the a date helper and a time helper to separate the information and I used a separate sheet to pull the information. The date helper formula i am using is: =DATEONLY(Created@row) +…
-
"Iterative" Formulas or Loops
Hello everyone. I am having an issue where I do not see a way to make any of my formulas iterate any specified number of times, let's say similarly to a "for" loop in a programming language. I am curious if there is a solution I am overlooking or if this is not currently doable with Smartsheet. Please see my example below,…
-
Changing WEEKNUM formula to pull on Sunday's instead of Monday's?
Hello, I have a sheet that acts as a "day tracker." Each row is a new date that tracks specific data in each column. I have a "Week Number" column attached to the "date" column, however the week number populated does not match the week number shown in Outlook calendar and Excel. I think this is because Smartsheets starts…
-
Formula to change status symbol if a task is not completed before due date
Thanks guys, new to Smartsheet and need some help... Having issues with changing the status symbol to Red, Yellow, Green, or Gray based on project completion percentage column (%Complete) and start/due date columns ("Start Date" / "End Date"). I would like the following: Gray - If %Complete equals to 0 and start date has…
-
Cross sheet Formula
What formula should I use to populate column E in "GHG Electricity" adding monthly total (Columns E to P) from cross sheet "SPI Data" for each site (in Column A) with Electricity (in Column C) ? THANKS!!
-
Nesting IF statements with the FIND function
Hello all, I am having an issue nesting an IF statement I created for a specific type of sheet. Lets say we have a column named Full Name and it has values in it like "This Long Name - A new value" The original function would be something like: =IF(find("This Long Name",[Full Name]@row)=1, "TLN"+MID([Full Name]@row,…