-
How do I count how many cells are last month but not considered expired?
I am working on a sheet for currency of qualifications for our employees. I have the equations that will tell me how many employees are a month before they are due, how many are due this month, and how many are in the past but now I have to find and equation that will let me count how many qualification are only one month…
-
Volunteer Allocation
Hi All, I am attempting to find a solution for a specific manual task that is error prone and very time intensive - We need about 300 volunteers occasionally on short notice and need them across multiple cities in 20-50 specific locations (we do not know these specific locations until last minute). I have built a survey…
-
COUNTIFS Description
I believe the description of COUNTIFS is misleading and basically just incorrect. It does not properly describe what it actually does. It says it counts the number of cells but it doesn't. It counts the number of times all given conditions in their respective ranges are met. One match of all given conditions in their…
-
Using Collect with count if and count if NOT
I have the following formula I'm trying to run =COUNT(COLLECT({Target Live Date}, {Checkbox}, "1", {Type}, "Native", {Status}, NOT "Published")) Count cells with a target live date, where the checkbox is ticked, the type is native, and the status is NOT published. All seems to be working well until the NOT part. Any clue…
-
If Statements
Hello All, I am attempting to create an If statement that raises a flag if two rows have the same location and the same date. This submission sheet will be used when people visit a location and we do not want any double bookings. So I was planning on having a formula raise a flag and then have a report that looks for the…
-
Index/Collect/Match help
Hi I have a sheet that has epics, stories, tasks and I want to extract only the epics to another sheet. layout is simple Name IssueType Epic 1 Epic Story 1 Story Story 2 Story Task 1 Task Task 2 Task Epic 2 Epic Story 3 Story Story 4 Story Task 3 Task Task 4 Task I want the other sheet to only have Epic 1 Epic 2 thanks so…
-
COUNTIFS Formula not working when I add an ISBLANK statement
I have a COUNTIF formula that I can't get to work once I add an ISBLANK statement. This is the fomula that works: =COUNTIFS({Payment Type}, =$Status@row, {Case Date}, MONTH(@cell) = 8, {Case Date}, YEAR(@cell) = 2020, {Status}, ="Completed") When I try to account for rows where the Payment Type is blank, I tried this…
-
COUNTIFS Formula For Total Number Of Requests Per Month Assigned To Contacts
Hello, I am trying to troubleshoot a formula to achieve the following outcome. Outcome: Count the TOTAL number of rows in which the [Request Date] column is March (3) AND the [Requestor] column is any of the following contacts: Jesper Poulsen Libby Martinotti Michelle Wisbey Annel Ayala Anita Miazga Amanda Ayre Alex…
-
Question on assigning a qualitative value based on quantitative info from members of the same group
Hi, I am looking for help on crafting a formula that would allow me to discriminate between [Type] -> "Dna/Seq". Right now, I know that they are either DNA plate barcodes, or Sequencing plate barcodes based on the "pcr" prefix. However, I would like to be exact with these and say it is either DNA, or Seq. It is fairly…
-
COUNTIF and VLOOKUP?
Good afternoon everyone, I hit a bit of a snag and I am curious to know if I may get some help. I have a tracker that we are using Smartsheet for and based on the type of item that is added in, only certain check boxes are required to complete the work. Originally I had done a massive COUNTIF formula where I had typed up…