-
Using Countif in place of Autonumber - formatting question!
I'm creating a ticketing system with multiple prefixes. Because of the multiple prefixes on one sheet, II opted to create a nested formula to number the tickets based on the various types of requests. Here is the formula: =VLOOKUP([Request Type]@row, {Ticket Assignment}, 2, false) + (COUNTIFS([Request Type]$1:[Request…
-
Counting values within multi selection dropdown column
I used a multi selection dropdown column to select each company that sent in a bid for that project and I want to keep track of the total number of bids sent for each company. The formula I used is COUNTIF({Architect Selection}, "BSA") to count the number of times BSA appears in the column I referenced from another sheet.…
-
Multiple Criterion with Cross Sheets
Hello I am stumped. I have tried several different combinations to my formulas trying to get them to function. I get an #UNPARSEABLE error most of the time. I have tried this formula =COUNTIFS({Complete Claim Status}, Status@row, {Vendor Name}, [Vendor Name]@row) Complete Claim Status is the range on my cross sheet |…
-
Formula to pull in fiscal work week is incorrect
I am continually getting the incorrect work week. In the below screen shot the Onboarded Week should be Week 16. Attached is our lookup sheet from smartsheet. Our fiscal week starts on Saturday, January 29, 2022 and the fiscal week ends on Friday, February 4, 2022. the pattern is 4,4,5 (kinda) you can see it gets a little…
-
SUMIFS with date ranges
Hello, I'm trying to sum the values (Annual Billing Amount) for anything that falls between 2/22/22 and today but I can't get my formula to work. Not sure what I'm doing incorrectly. =SUMIFS({Annual Billing Amount}:{Annual Billing Amount}, {Date to Deactivate}:{Date to Deactivate}, AND(>=DATE(2022, 2, 22), <=TODAY()))
-
SUM IF with OR and CHILDREN
Hello, I am trying to add one additional condition to this statement below, but can't seem to figure it out. Here's what I have currently (which works): =IF($[Row Identifier - Budget]@row = "Parent", SUMIF(CHILDREN([Alt Cost Center]@row), "", CHILDREN()), "") And I'd like to add an additional option that says if [Alt…
-
What's wrong with this formula?
=COUNTIFS({Sheet - Sales Pipeline Range 1}, Client@row, {OD&I Pipeline tracker Range , "Pipeline")) It just reads UNPARSEBLE. Thanks Phil
-
Calculate Week number from multiple choice dates column
Hi, We are running a Wellness challenge for a charity. Participants log the days and duration when they performed the activity. We allow selecting multiple dates for the same activity. I try to total Duration for a week. "Duration" mean the number of hours for each of the selected days. First, I need to convert a date into…
-
Automate Flagging at risk task based on approaching due date
Hi Community, I am looking to automate flagging a task if it comes within two weeks of one of its two due dates (each task has a recommendation date and approval date) and its currently not complete (of which there are two opportunities for it to be complete, the recommendation and the approval). The task must have a…
-
Status Roll Up to Parent
Hi, I have a column that has the following status distinctions: Remove Not Started In-Process Final Draft Ready for Group Review Group Review Complete Final TechPubs Complete PTP-In Process PTP-Approved I am looking for a formula that would be set for the parent but only show: Not Started In Process Complete How should…
