-
SUMIFS formula returning #INCORRECT ARGUMENT SET: order of Range & Criteria?
Hello, I cannot figure out why this SUMIFS is not working. When I break up the SUMIF criteria, they both work fine. But when I put them together, I get #INCORRECT ARGUMENT SET error message. =SUMIFS({MH Client Profiles v2 Range 13}, "East", {MH Client Profiles v2 Range 16}, 1, {MH Client Profiles v2 Range 3}) All I want is…
-
At Risk Formula Unparseable Error
I am trying to build a nested at risk formula based on the following triggers. -End date is today and Status is not complete it will trigger -There is not enough time between today and the end date for the duration period left. So, if duration minus % complete is 4 days. and the period of time between today and the end…
-
How can I compare two cells in a cross sheet reference?
Hello, I have a COUNTIFS formula (below) that returns the number of items that pass all the tests. Basically, count an item if it's unique id is not blank, it's % complete is 100, and the risk area is Antiboycott. This formula works beautifully. =COUNTIFS({ExportUniqueID}, NOT(ISBLANK(@cell)), {ExpComp}, @cell = 1,…
-
Different types of zeros?
The formula below is creating multiple "zeros". See pictures. What am I missing? =IF(Balance@row = "ERROR", "ERROR", IF(Balance@row = "TBD", "TBD", IF(Balance@row = "DEDUCT", "DEDUCT", ((SUM(Jan@row:Dec@row) + SUM(CHILDREN(Jan@row:Dec@row))) - (((SUM(CHILDREN([Contract Amount]@row), "Change") + ([Contract Amount]@row)) -…
-
IF(OR with RYGG
I would like to automate a RYGG column, assuming that my team is entering there own start and finish dates but due dates will be created by senior staff. Yellow=In progress Red=Overdue Grey = Not started Green=Completed So IF start date is before due date, Yellow or if Finish date is before due date Green, etc. An…
-
Getting #INVALID DATA TYPE using IF(AND and IF(OR together
=IF(AND([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1, "Green"), IF(OR([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1), "Yellow", "Red")) Formula above. Would like to display green ball when all checked, Yellow when some checked, Red if none checked. Can't figure out…
-
Counting overdue tasks from a lot of project sheets
Each project sheet has basically an identical structure and each line item is assigned to a specific person/user (Contact list). There are approximately 300 lines per project sheet. Each user has a report that shows only the tasks assigned to them from all the source sheets. Previously, we had a dashboard that showed each…
-
Add new row, calculated field not updating
I have a sheet that uses Hierarchy (on month) and is displayed in descending sort order. I have one calculated field that should be automatically calculated when a new row is added. When the person who will be entering data adds a new row, they are using "Insert Above" to add the row. The calculated field is not updated.…
-
VLOOKUP plus a Dropdown selection???
I don't think this is possible, but thought I'd ask here. I have a situation where I would like to look up a value in another worksheet if the the value in a column adjacent to the lookup contains "DMP". I know how to write that formula, but if the value does not contain DMP, then I don't want the lookup at all, but want…
-
If 'Submit Date' is before the 20th of current month, Set 'Payment Due' to last day of month
Hello! Creating a sheet to help with Accounts Payable process to external providers. External providers will submit their invoice and payment details into a form that feeds into this sheet. Would like the formula to determine when that invoice is set to be paid, depending on the submission date. The formula in words.. If…