-
IF statement - Less than 3 weeks from today
Hello, I am looking for help on a formula for IF([Proposed Effective Date]@row is less than 3 weeks from today display "Rejected" AND IF([Have you received quotes form competitors?] = Yes then display "Rejected" So if one condition applies it displays "Rejected" or if both then "Rejected" displays as well. Thank you so…
-
Matching Neighbor Cells or Rows to Most Recent Date
Hey All, Background: Im trying to build a fluid dashboard that tracks information coming in from a form. This form is then routed to several sheets where I break it down further based on certain criteria. I am looking for a way to report on the most recent metrics in the given sheet using the sheet summary feature to make…
-
CountIfs with Distinct and a date range
I am sure it is doable, but I continue to struggle to get CountIFS, AND, and DISTINCT to work together. I need to count distinct dates in a long column and need to determine how many distinct dates there were in particular months. I'm able to use this to see distinct counts after a certain date:…
-
COUNTIFS IS BLANK OR other criteria
Hi all, I'm trying to make this formula COUNTIF the Due Date column is in the past and IF status column is blank OR it is "not received" =IF(ISBLANK(Status@row), COUNTIF([Date Due]@row < TODAY(), 1), IF(Status@row = "Not Received", COUNTIF([Date Due]@row < TODAY(), 1))) The above formula is returning 1, but the Due Date…
-
IF Statement for multiple cells with blanks
I am trying to create an IF statement which returns a status based on the status of 6 separate cells. How do I set the status to ignore blanks from those reference cells? For the attached example, the status should be set to "Not Started" based on the columns highlighted in blue.
-
How can I create a sheet that counts occurrences of a word in multiple sheets?
I would like to create a sheet that tallies up all the instances of certain words, such as 'retain', 'rotate', 'textile', etc., from multiple sheets. So it would look like: Retain - 30 Rotate - 50 Textile - 20 etc. Is this possible?
-
CountIf with Date range
I am trying to count the number of requests types between a date range. This is what I thought would work: =COUNTIF(({Request Tracker Range 4 - Request Type}, Category5), AND({Request Tracker Range 3 - Date Requested}, DATE(2019, 01, 02:2019, 03, 14))) I am using this formula to count the entire column =COUNTIF(({Request…
-
Complex JOIN(COLLECT())
Hello all, I'm hoping to use a formula to return a JOINED list of shared USERS for all sheets in Sheet 1. I have three additional sheets that contain all the supporting data, but since I don't want group names, is it possible to perform the user lookups from the groups sheet within the COLLECT function? Sheet 1: A list of…
-
<> in Formula
I am counting rows (projects) in my intake sheet that meet one dropdown option, but dont meet another. (I want Project Status= Site Control Initiated, but Site Control Status does not equal to SCA Signed). I created a filter on the intake sheet to quickly see these, and the count shows 223 rows. However, when I created a…
-
Trying to autofill an assigned person from a different sheet
I'm trying to get the assigned person with the contact list to autofill based on company position and project name. So I have Sheet 1, I would like the "Assigned To" to be auto-filled from Sheet 2, by using the Project name and Type on Sheet 1. Sheet 1 Sheet 2 - Sheet 2 is always filled out first to pick a group of people…
