-
IFERROR Formula Should Catch INDEX() Returning #INVALID COLUMN NAME
Recently we noticed that when we have a function that looks like this: =IFERROR(INDEX(COLLECT(...), Index), "") If the Index value was 0 the result of INDEX() was an error (understandably) - #INVALID COLUMN NAME, however IFERROR() did not catch this as and error but was also returning #INVALID COLUMN NAME. This makes not…
-
Formula question - count projects by status, category and date
Hi Everyone, I want to count the number of Project IDs where the Project Status = "Complete", the Project Category matches the column header (AV-TV, Software, etc.) and the End Date is greater than 2022,09,30. I can count the number of overall complete projects for the project category without the date, but cannot seem to…
-
Nested IF/INDEX/MATCH Challenge
I am stumped and hoping someone can help. Here is my sheet: In the [Partner Name] column, I have this formula: =IF(OR(BEGEOID@row >= 34, BEGEOID@row <= 94956), INDEX({BEGEOID Partner List A Partner Name}, MATCH(BEGEOID@row, {BEGEOID Partner List A BEGEOID}, 0)), IF(OR(BEGEOID@row >= 94957, BEGEOID@row <= 431490),…
-
How do i create a status column based off of levels and approvals?
I have to create a status column that uses 3 different levels that each require different people to approve. how do i make one big formula for it all. For example level three needs person one to approve, then person two, then person three. then the status can be complete. basically i need if status is level 2 then person…
-
Average based on 1 criteria
I'm sure this is user error but I'm trying to average out compensation based on the job code then I'd like to create either a data mesh or vlookup to fill in the blank compensation with these averages. I've tried having a seperate sheet that has the job codes and then I'm trying to use the averageif formula. However I keep…
-
I have a column of Filed dates and need to count how many fall within a given year.
I've tried several examples that I've come across here but most of them just give a formula without explaining what the components are so I'm having trouble adapting it to my purposes.
-
Column Formula to return 1st, 2nd, 3rd, etc. instance of a column entry.
I am looking for a Column Formula that will return the 1st, 2nd, 3rd instance of a column entry. The following cell formula works, but I am not able to make this into a column formula and need it to be a column formula so that when our form users submit a new form the Delivery Count column automatically updates. This is my…
-
COUNTIF leads added last week
Good day! I'm working on this new KPI report for leads added last week, and I'm using this formula: =(COUNTIF({Aldo Aguilar - Pipeline Range 1}, "2 - Upselling", "4 - Lead Generation", COUNTIF({Aldo Aguilar - Pipeline Range 2}, WEEKNUMBER(TODAY(-1))))) But at the moment that I hit enter, it returns "#INCORRECT ARGUMENT".…
-
How can I get a 2 digit month to appear with the following formula
Here is the current formula. =IFERROR(YEAR([Close Date]@row) + "-" + MONTH([Close Date]@row), " "). The results of this formula are causing sorting errors. I would like the result when the the month is less than 10 to appear with 2 digits. For example, the current result for September 2022 is 2022-9. I would like it to…
-
COUNTIF(DISTINCT
Hello Community, I am attempting to count unique numbers in one column if the code in the other column is "X". =COUNTIF(DISTINCT([Claim Number]:[Claim Number], [Contract Type]:[Contract Type], @cell = "LW")) Claim numbers are formatted as letter and numbers. Each part work fine separately, but not together. What am I doing…