-
Trying to count if data range falls between two dates.
I have a sheet with StartDate and EndDate fields. I want to count actual items opened on a separate sheet if the date falls between the start date and end date. The below formula keeps returning #UNPARSEABLE and I suspect its something minor. =COUNTIFS({Request Tracker with Form Range 1}, "<=" & (EndDate@row),{Request…
-
Countif- Remove Duplicates
Hi there! I am trying to count the type of client we have per opportunity attached to a project. We will have some projects attached to the same opportunity and we will not want that data to be counted twice. I have attached a sample sheet of the data. I have been using this formula to count duplicates. However this is…
-
Capacity formula
I am trying to add to this formula "<+" &{end week}, Weekend@row =COUNTIFS({Name}, [Primary Column]@row, {Week}, start@row, {Year}, [Column3]@row, {SKill}, "LTE") * 60 I want this formula to look and see the end week and add 60 for every week of the project. Help columns and example below. Right now this formula tracks 60…
-
Formula help: Count how many dates are from the last week and the coming week
I am trying to do an equation in sheet summary that will calculate how many dates were in the last 7 days and are scheduled in the next 7 days. This is what I have, I've tried others on community questions that were close and figured I would just ask. =COUNTIFS([Start Date]:[Start Date], @cell <= TODAY(-1), @cell >…
-
How do I see if a resource has (or is) working a specific date?
I have resource names (one per column on the destination sheet, and one per row in the source sheet), start dates for jobs (one per row in the source data sheet), and end dates (same) for the same jobs. I'm trying to use an equation to see if the resource is working every date, throughout the year (one date per row, in the…
-
Wildcard workaround
Hi, Trying to get some counts for a report and have run into a bit of a road block. In excel, I would typical use a count formula and a wildcard. However, Smartsheet doesnt seem to have the capability. Below if my code: =COUNTIFS({Papers Range 2}, "*O'Malley*", {Papers Range 3}, "1 - Published") How would I edit this to…
-
No Category Appearing on Calculated Column in Calendar App
I have a report which feeds into the calendar app in which we track speaker utilisation. The data is grouped by speaker name and then their total utilisation. When utilisation is zero, it shows this in the report but shows as No Category on the calendar. Is there anyway to change this to 0? I've got a countif for…
-
Count formula help
I need to count number of tickets in a few columns, independently, and present the calculations in a metrics sheet to be used later. I need to calculate weekly and monthly numbers. I have tried many iterations of formulas and continue to get errors. Would love your thoughts. =COUNTIF({Week #}:[Week #], ${Week #}:$[Week…
-
Distinct Count IF or Running Count Total
Good Morning All, I need a formula to count the running total of the Dealer Names as they appear. The current formula I have in Column 14: =COUNTIFS(Dealer:Dealer, Dealer@row) is showing the total times Dealer Name is referenced. In my situation I would need "TriGreen Equipment - Ag & Turf" is referenced twice but has two…
-
COUNT IF - CRITERIA
Hello Team, I have the following data to work on: 22-123 22-456 22-789 23-123 23-456 23-789 24-123 24-456 24-789 Where the first 22-, 23- and 24- represent 2022, 2023, 2024. I'm trying to use the "COUNTIF" formula to count them by using, the 22, 23 and 24 as criteria, to know how many items I have per year, however it…