-
Formula using SumsIF
Hi Everyone, I need assistance on a formula I want to sum the numbers in the "Qty of Monitors" column if the Office column is "SOMA" and when the dropdowns are " Acer AND Viewsonic" are both selected in the Monitor column. Formula I have put together=SUMIFS({Qty of Monitor}, {Office}, "SOMA", {Monitor}, "Viewsonic",…
-
Forms that trigger updates - workaround for making those fields required
1) We use a form to collect required data. From there, one question triggers an update request to a third party from the email that we collected in the form. We want these fields to be required. As a workaround, I tried using the same workflow to set a condition that if any are left blank, then to request a secondary…
-
CHILDREN of two columns
I was trying to reference the children of two columns @row in a MIN(COLLECT()) function. Currently, I am only able to use two separate COLLECT() --- MIN(COLLECT(CHILDREN(col1@row, criteriaRange1, criteria1), COLLECT(CHILDREN(col2@row, criteriaRange1, criteria1)) But when I arrange the two columns together,…
-
Formula for subtracting child rows from parent total
I need help with a formula where I have a funding total in one column, a second column with Invoice amounts. The invoice rows are children of the top level "funding total" row. I tried the following: =SUM([Funding Total]@row) - SUM([Invoice Amount](CHILDREN())) Example of it not working: I can do this formula dealing with…
-
Need a checkbox checked if two items are true / Code not working
looking at an item on line that will give me access to everything that is past due or due within the next two weeks on a project schedule. We built a report but it does not give the parent task associated with the child task. I googled and got something similar to what I need but not quite there. Here is what I need: I…
-
Is there a limit to how many rows a function can search, for an IF statement? Having data not show.
For a requisition database, I maintain a pool sheet of available equipment, as well as sheets for orders and the vendor. I have formulas in a different sheet to add the VINs (let's say 5) that I am planning to add and it checks across the other sheets if that VIN exists. On this "check" sheet, I've noticed some VINs will…
-
I am trying to compare 2 date columns with an odd result
I am trying to count the number of submissions on a daily basis with =COUNTIF({Loans Range 1}, =[Begin Date]@row). Most of the formulas return the correct number of submissions. What it looks like it is doing is applying the submissions entered after a certain time (5 PM?) to the next day. Dependencies are disabled…
-
Finding duplicate name and date of birth
Hi, I have a sheet with demographics date. I created a helper column to find if the combined name and date of birth are duplicates, it works when the name is exactly the same, but when there's a slight difference in the name, say one has a middle initial and the other doesn't have one, it won't be flagged as duplicate.…
-
Formula to count $ amount for specific vendor
I am creating a dashboard to show my department's metrics. I have tried multiple formulas but keep getting errors. I need to calculate how much was spent for a specific vendor. I am using a reference sheet in the formula with the range being "vendor" but I cannot figure out the proper criterion to count the $ in a…
-
Service log / mileage tracker
I am using a form which has an individual input the current mileage of a vehicle every time they use it. This would need serviced every x amount of miles. I am wanting to set up an automation that will email a specific person whenever it's time to service the vehicle. I guess right now I am trying to find a way to do this…
