-
Counting amount of actions in one month
Hi, I'm trying to set up a counter for a number of actions in the month of may, for example if I have 5 actions this month, I want the counter to return "5". I've been trying to use the formula below but all I get in return is a zero: =COUNTIFS([Target Due Date]:[Target Due Date], IFERROR(MONTH([Target Due Date]1), 0) = 5)
-
Formula to reference one column based on entries from another column.
I need help on a formula for Project Assignment when a dollar amount is what determines who gets a Project assignment. For example, A Facilities coordinator will get a project if it falls between $5k - $24,999 Project Manager A will get a project if it falls between $25k and $99,999 Project Manager B will get a project if…
-
What is the issue with my formula?
=IF(AND(% Complete]3 < 1,[End Date]3 < TODAY()), "On Time", IF(AND([% Complete]3 < 1,[End Date]3 = TODAY()), "Due", IF(AND([% Complete]3 < 1,[End Date]3 > TODAY()), "Past Due","Complete"))) If i validate each part of the formula separately it returns my intended value however when I try to embed using IF AND, it is always…
-
COUNTIFS criteria including dates after X
I'm trying to create a formula that counts the number of entries submitted to a separate sheet per person completed since a specific date (3/24/2020). My formula that works for counting all entries by individual is =COUNTIF({Other Sheet Range1}, [Member Name]1) but when I try adding the AND {Other Sheet Range2}…
-
Count Formula
Hello! I am tracking assignments and a person can have more than one assignment. I need to be able to count the number of persons assigned on the sheet but don't want to count dupes. The fields are "First Name" and "Last Name". I'm trying to use the =COUNT(DISTINCT(RANGE) formula but I'm not having any success. Also, there…
-
VLOOKUP with Duplicate Data
I have a master sheet that may have duplicated column data, like: Client Name | Status (dropdown) | Delivery Date | Feature (checkbox) Client A | Rescheduled | 2/5/2020 | 0 Client B | Postponed | 2/12/2020 | 1 Client A | Confirmed | 3/17/2020 | 1 Client C | Requested | | 0 I have a sheet I'm using as a template to create…
-
Subtask inheriting data from Parent
I've a field for ClientName. I would very much like for subtasks to automatically inherit the Parent Card ClientName value. How might I do this? My users prefer using the Card view. They feel that if they have to use the spreadsheet view, they may as well use Excel. So while I may need to build the functionality in the…
-
IF / CONTAINS using a range
Have an issue I thought I had resolved, but ... I have a sheet that has two relevant data ranges: Range 1 = a single column that specifies a Content Type for each row item. There are 7 possible Content Types: Data Sheet, Ordering Guide, BDM, TDM, Demo, Learning Map, Proposal Range 2 = 15 columns that contain a Product…
-
Summary Report - COUNTIF of multi-selection columns
I love the way a Summary Report can kinda be a poor man's pivot table within Smartsheet, and it works really well, up until you try to do conditional counts of columns with multiple selections. See below. What's happening here is I want to allow a COUNTIFS when a person is chosen, and count the number of instances data…
-
Nested if/then/and/or???
Hello out there, attaching a screenshot for reference. I'm trying to get the far right column ("Completely Processed") to check under a series of different circumstances: 'Check Cut to Vendor' box MUST be checked for all cases If none of the 'outside $$' boxes are checked and 'Check Cut to Vendor' is cut, then the…