-
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…
-
IfError- should this work?
I Am trying to get an IfError formula to work and not having any luck. CAn anyone help? =IFERROR([Product Type]@row = "Hardware", [Actual Shipment Date]@row >TODAY(-12), [Backlog @ Bud]@row), " ") I want that if a row is hardware and the actual ship date is not within the last 12days it populates the cell with the value…
-
Sum Child Rows when "children" are a different column
I am sure this has been asked before, but I can't seem to figure out a solution that will work in my sheet. I am trying to sum the child rows of a specific column, but my formula isn't working. Can someone take a look at the screenshot and see if you can help? I need to add the individual expenses (in the Expense Total…
-
Concatenate Name if Preferred Name not blank
Hi, I am trying to create a formula where I can concatenate a person's name as "Smith, John" if they don't have a preferred name listed (if blank) and as "Smith, Jane (John)" if they do have a preferred name. My fields are Last Name, First Name, and Preferred Name. Can anyone help? Thanks so much!!!
-
COUNT(DISTINCT(COLLECT - Counting a value per employee
Hi, I'm trying to count how many values I have for certain people. I have two values that are assigned to the employees daily, for each day we have a value of either "Y" or "N". I want to count how many "Y" an employee has. I'm getting this information from a big database, to have it summarized in a small one. I've tried a…
