-
Return the month of a closed item
Hi, I'm trying to create a formula for when a query is closed and the month is return in a separate column. I have managed to complete this for the month the query is opened as all items opened have such a date. However, if the query is not closed the cell is blank and I receive the following error message "#INVALID DATA…
-
Using an OR in COLLECT
Hello - I'm trying to use an OR operator in the criteria of a collect function. This is currently returning an #INCORRECT ARGUMENT SET: =IFERROR(INDEX(COLLECT({WAP Deposit Number}, {WAP Date}, Date147, {WAP Type}, OR("CHECK", "CREDIT"), 1), "")) I'm trying to return the a deposit number {WAP Deposit Number} if the Date…
-
If/And/ISDate/OR
Hi All I am trying to set up a status message based on a few variables. I can get them to work indidually, but when I bring them all together I get an InvalidData error. The parameters are If FWA Required= YES and FWA attestation is Date and Date Attestation Form Signed is Date and Education Completion Date is Date. If all…
-
COUNTM Formula
How can my COUNTM Formula only capture those projects where efficiency gain is 1 of the benefits selected for that project. Project currently have more than 1 benefit such as "Efficiency Gain". I am currently entering the formulas below, but not sure what else to do. =COUNTM({MH-Work Prioritization Log Range 2},…
-
Countif and Has returning 0 from multi value cells
I have a column that has the option to have multiple values. I created a calculation sheet so I can use the data in a dashboard, so I need to count the number of "GovDoc" and the other values but it keep returning 0 I'm using the following formula =COUNTIF({Issues}, HAS({Issues}, "GovDoc")) What is wrong?
-
BOM - INDEX/COLLECT Formula referencing child rows only in another sheet
Hey there, If anyone can help. I have set up two calculation sheets. The first is a Bill of Materials with the second being a variations sheet where I can combine various options from the BOM sheet. Each BOM item has a corresponding unique ID number. In the Variations Sheet I want to have a list of the BO so I can easily…
-
COUNTIFS, CROSS SHEET, HAS, MULTI-SELECT DROP DOWNS AND MULTIPLE CRITERIA
I feel like I've been going crazy. Trying to get a count of how many [Primary Column]45 appear when the other ranges have the listed criteria. All ranges are multi-select drop downs. Keep getting 0, where I know there is at least 2. =COUNTIFS({Rx Consumer Complaints Tracker Range 1}, HAS(@cell, $[Primary Column]$45), {Rx…
-
COUNTIFS not working correctly with dropdown critierion
I am using the following formula to count the number of rows that have both [Pricing] unchecked and [Status] showing any other value EXCEPT "Not Applicable. =COUNTIFS(Pricing:Pricing, 0, Status:Status, <>"Not Applicable") When I use the above formula, it keeps showing the incorrect value. I've filtered the sheet using the…
-
Formula to Refresh ONLY if Status is Open
Hi all! First of all, thanks for your assistance. I have a sheet, and I want to create a formula to another (a simple VLOOKUP). However on my initial sheet where the formula resides it will be based on the Status. If the status is Open, I would like the formula to make any updates. However if the record has a Closed…
-
Months Remaining Formula
Trying to come up with a formula to calculate the duration of contract between 2 dates regardless of todays date with a result in months. Then, how do I get the remaining months on the contract from todays date - in months - over multiple years - and if the start date has not began yet.