-
count if formulas
I would like to count how many of my orders do not have PO number (i.e Po Number cell is blank) Can you help with the formula to do this Thanks
-
Hide Errors Until Cell is Populated
I'm calculating the variance between dates using the following formula. The problem is that an error is displayed if the Actual Start Date is not populated as show in the screenshot. Formula used in Variance Start Date: =NETWORKDAYS([Planned Start Date]3, [Actual Start Date]3) - 1 Is it possible to have the Variance Start…
-
Formula for Counting an Item Once Per Date
I am trying to come up with a formula to count the number of times a 'meeting type' occurs within a column. The issue is, I only want to count the 'meeting type' once per day whether it only occurs once or 20 times. So in the case of my screenshot, I would be returning the following: CIty Council - 3, SPIN - 2, P&Z - 2.…
-
Concatenate two cells
Hi, I need to concatenate data of two cell into one cell. I just use the formula "=[Cell1] + [Cell2])", which worked fine and gave the result "Cell1Cell2", however I need to have space or underscore between values of two cell like, "Cell1_Cell2" or "Cell1 Cell2". My practical requirement is: I need to concatenate "date"…
-
Linked Cells with Numeric Value not working in Chart Widget
Hi, I have a roll up of numbers for the total of checkboxes checked off in our main data sheet. I've linked that totaled value into a separate sheet via the link cell capability. That is somehow not getting charted, and is showing up as "Invalid data"? What am I doing wrong?
-
Calculate days past due based on end date
I want to populate a column labeled "Days Overdue". I want to look at my column "% Complete" if "% Complete" is not equal to 100%, then get TODAY() date - "End DATE" and populate the value into "Days Overdue" column, else 0. In excel I can accomplish this with the following IF statement. =IF(% Complete<>100%,TODAY()-END…
-
Invalid Data Type using Find function
Hi All, I can't seem to find the error in my formula. I'm trying to use FIND() to locate a unique id # SE2 within a column on another spreadsheet. The column in the other sheet is a text column. =FIND("SE2", {**2019 Event Builder Range 1}) I keep getting back Invalid Data Type. I'm not sure what I'm doing wrong. Can anyone…
-
VLOOKUP is data type of TEXT instead of DATE - sorting is off
I am currently creating an report which looks up dates based on specific milestone tasks over a number of project sheets. When the vlookup calculation is entered and I hit the Return key, is it giving me an alert that the value is not in date format. Being the admin, I am able to override this. But when sorting, it sorts…
-
Index and match multiple sheets
Hi, I have an index and match formula set up for 1 sheet and it works great but how do I handle it if I want the index to look at data from multiple sheets to pull the correct information. My current formula is: =INDEX({Site Number}, MATCH([Site ID]62, {Site Name}, 0)) I would like the index to look at the site number on…
-
Using One Checkbox to Check Another
We have a task sheet that uses 3 checkbox columns (Sent, Received or Done, N/A). We have a formula that counts the number of tasks and how many are completed based on the Received or Done column. We are trying to make it so if the N/A box is checked, it automatically checks of the coinciding Received or Done box. Please…