-
Column with a running total by location
I have a column for Address, City, and State. When entering data I am able to get a column that will count the total for each, but I want to add a column that shows the running total for each. For example in row 1 I enter data for location x the running total would show 1. Then in rows 2 and 3 I enter data for location y…
-
How to block a cell on formula
Hi, I want to know how can I lock a cell in a spatial working formula. Example: = IF (AND ({PROJECT CASE TEST Rank 7} = January1, {PROJECT CASE TEST Rank 6} = January2), {PROJECT CASE TEST Rank 8} + {PROJECT CASE TEST Rank 9} * January5, "") When I pull the formula left, right or down, the cell formula can't blocked and…
-
COUNTIF using Count(Ancestors)
I am trying to use the COUNTIFS formula to return a count of items within a date range. The issue is a large number of the rows have indented/child rows that I do not want included in the count. The formula I have tried is: =COUNTIFS({FOLLOW UP Range 1},COUNT(ANCESTORS()=0)>DATE(2021,1,1){FOLLOW UP Range…
-
MONTH/YEAR formulas based on date column
Hi there, I have a column for "Report Date", "Date received", and "Month/Year". In my "Month/Year" column, I want the report date to be written out instead of 1/6/21 as January 2021. However, sometimes our "Report Date" column is blank/has the word "Skip" written when we don't have to write a report for that row. When that…
-
Ranking values in a range based on matching a date
Hi Smartsheeters - I'm in a post-Covid brain fog and trying to work this problem. I have a list of materials. For each material, I list a warehouse, a quantity of scanning errors, and a date. I have a rank column I need to populate with the top 5 highest quantity for a given date at a given warehouse. Here's a snapshot of…
-
Multiple IFs help
I am trying to create a formula with about 10 if conditions. It works with two, but as I add the 3rd I get an error. Here's the formula =IF(AND(Publication@row = "Architectural Record"), " https://www.architecturalrecord.com/call4entries/forPublication", IF(AND(Publication@row = "BD+C"), "Word count: 500 – 1,200; 4 - 5…
-
Combining IF and OR
Hello, brain trust! I have a conundrum that seems easy enough but isn't quite working for me. I'm trying to reference a range in another sheet, and I only want to count cells if they are within a specific date range and have a status of either "Not Started" or "In Progress." I know that to count one category, the formula…
-
COUNTIFS returning 0
I am having an issue with the formula below. I am attempting to get the count where the year of {Conv Rec 020} equals the cell "2020" and the month of {Conv Rec 020} equals 1 (aka: January). I have quoted the 1 and get the same results. The formula is returning "0"... any thoughts to return the correct values? It should be…
-
A workflow has become invalid
Can anyone help me with this? I'm getting an error (below) which I've had in the past and resolved quite easily. This time though I can't find the duplicated name. I do have a system column in both which generates a number but they have different names, is this the problem? Rows can't be added to the destination sheet…
-
Return value in bottom of column
Is there a way I can get a cell to return the value of the last row? I have a sheet where we all add new info at the bottom of the sheet. I need a summary row to send out the most recent totals to my boss on a weekly basis.