-
=COUNT(DISTINCT(COLLECT
Hi, i am using the "DISTINCT" value in a formula which works great however is there a way to not count entries twice based on if its submitted as capital or lower case, for instance 'Fred' & 'fred' are counted as two unique names when in fact i only want it counted once? Cheers Shaun
-
Assign an "item" to a new owner and delete the same item from other "owners/cells"
how i can do it, assign an "item" to a new owner and delete the same item from other "owners/cells", in an automated way, or in anothers words, there two conditions: -assign "equip" column to "owner" column -delete the same "item" from other cells in the "item" column example> i really appreciate any help.
-
How do I subtract days from a date
I'm trying to build a production schedule spreadsheet. In one column I have the date the production item needs to be completed. In the next column I have the number of days (just a whole integer number) needed to complete. I would like to use a formula to determine the date production needs to begin. Simply (Date to be…
-
COUNTIFS with a Date Range
Hello there, I am needing help with a formula, i am trying to count the number of faults that happen per week. I have the below so far but it is coming up #UNPARSEABLE =COUNTIFS([Faults]:[Faults], [Week Starting]@row >= [Week Ending]2 <=,[Week Starting]@row >= [Week Ending]2<=)
-
COUNTIFS using MultiSelect as criterion
I have a sheet that is tracking all of the tasks users need to do in a year. They enter the recurrence of the task as Daily, Weekly, and Monthly. They also enter the Start Date and End Date (ex. 3/15/2023). I then create rows for each month the task is running. So 3/1/2023 - 5/31/2023 would have three rows (March, April,…
-
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…
-
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…
-
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…
