arrary formulas

I'm wanting to count unique alphanumeric value from column 'user ID', but I'm wanting to include a criteria of date range where column 'Date' is between 12/12/2019 - 18/12/2019. I've tried numerous array formula's but they're all too slow so i'm wanting to know if it's possible to do it without...

Hi! I am trying to make a formula where the longest word in a range from a different worksheet, is reflected in the same cell where I create the formula. For example, Cell A4 should have the formula and the Range would be located in a Worksheet labeled Descriptions Cell A4 would show the...

Hi All, I have a list of hundreds of server with multiple applications on them. I want to lookup and list all servers with the same application on them but also want to identify server dependencies which occur through additional applications on the server. Hopefully this example will explain...

Hi, My goal is to add all the cells of a columns and the subtract the sum of cells of several other columns, meeting a certain criterion. So I found SUM(IF(...)). SUMIF didn't work because you could add the cells of more than one column (e.g. A2:C:10 doesn't work). So I wanted to add the cells...

I have a table with golf scores for a junior golf tournament with the following columns: name (A2:A101), age (B2:B101), round 1 score (C2:C101), round 2 score (D2:D101), and round 3 score (E2:E101). All golfers participated in rounds 1 and 2 and half the field competed in round 3. The ages...

I have two sheets , one is for repaired car and another one is for returned car so I am trying to create an index formula that returns the returned car's brand name , Mechanic name and the problem but the information isn't accurate Note: there is only one car per brand name , which means if...

Hi, i've been trying for weeks to get an index match to result the the date and the manufacturer for when the object expires with no gaps. Her is the initial table, i have shrunk this as the actual one is 88 company's by 36 manufacturers; <colgroup><col style="width: 100px"><col...

I have an array formula below that adds transactions for each week. This works OK until I come to a week1 which starts on 30 December 2013 and ends 5 January 2014. My work sheets spans more than 1 year, 2013-2015. The answer that I get takes only the transactions in 2014 ie week 1, 1-5...

Hi everyone - I have an array formula that was initially working but now shows the NA error in every cell where it is written. My formula is as follows: =MEDIAN(IF(Filled!Z:Z>4,IF(Filled!G:G=E3,IF(Filled!B:B>=E5,IF(Filled!B:B<=G5,IF(Filled!K:K<18,Filled!Z:Z)))))) E5 and G5 are cells with dates...

Looking at this image: View image: ex 4 I want to be able to in cell C2 have a function that looks at Cell B2 and if it says "Yes" look at Cell B1 and it it reads "No" return value A1. For cell C3, what should happen is that it looks at B3 sees "Yes", then looks at cell B2 sees another "Yes"...

Hi, I am currently using the below formula to find the value in my table: {=IFERROR(INDEX($D$45:$D$404,SMALL(IF($B$19=$A$45:$A$404,ROW($A$45:$B$404)-MIN(ROW($A$45:$A$404))+1,""),ROW(A2))),"")} A dropdown list is contained in cell b19, there are others in cell b25, and b11. I need this...

Hi I have seen to many combination solutions but none of them uses array formula. Is it possible to generate unique combination (order doesn’t matter) using array formula. Column A has list of participant, column C should give all possible unique combination. This is what I want: <tbody>...

I'm looking for help developing a formula that will return the results... 1st, 2nd, 3rd, 4th, etc 51st, etc. <tbody> Date Name Department TO Variance CC! CC2 CC3 5-24.99 25-49.99 50-199.99 200-399.99 400 or more YTD 02/27/15 Bill BAR Bar Manager (99.95) 6th $50.00-$199.99 9 3 6 0 0 18...

Hello, I have an excel workbook that I have been creating, and after looking at formula's as long as I have the past few days I'm tired of working on this file and just need validation so that I can complete it. I want to have the array formula validated to make sure that I will get the...

Hi all, I have used a formula to output yes and no, now i am trying to figure out how to count how many consecutively yes and no there are in a coloumn For example A..... B ......C..... D....... F ID 1......5......6......4....... No 2......1......8......10..... No...

I'm trying to "translate" a spreadsheet from Google Sheets to Excel. Basically the spreadsheet has two tabs: Tab "raw data" contains rows of inventory info. e.g. Item #, Qty on Hand, Qty on Order, Color, and other specs. Tab 1 is where you type in a list of item #'s, and the arrayformula grabs...

Hi All the below function is used in a table of many similar functions, The highlighted C is the main value changed to return the desired value. This works fine in all my calculations except when i exchange the C for a D. I have looked at the source data and anything else I can think of, but I...

I want to find the time it took to change from one status to another for a file. I want to know the duration between the status “Pending Vendor Onboarding” and”Action required” for file number 71845 using datetimestamp. I’ve tried to do it with an array but it’s just not working. column A...

Hi everyone, I would appreciate it if someone can help me with this. I have a workbook of 13 sheets. All 13 sheets contain the same information:Activity-Employee Code-Employee Name-Code1-Cost1-Code2-Cost2. I'll show a small example to illustrate my case: I have a table containing all...

Hello All, this is my first post, so apologies if I come across noob-ish! What I am trying to do is compare 'Prices' between different 'Providers' for 'Trips' that they all run/operate - Public Transport for instance. The raw data might look something like this if simplified: <tbody> Provider...