arrayformula

Good day. I have a sheet of data, columns D:G Column D is the "parent" of E, E is the parent of F and F is the parent of G. example data below: Column D Column E Column F Column G Bedroom 1 Building Internal Partitions Framework Bedroom 1 Building Internal Partitions...

TASK TASK ID ASSIGNED TO TASK 1 200623175025 ADAM TASK 2 200623175159 JONAS TASK 3 200623184657 JAMES TASK 4 200623184918 ROSSI TASK 5 210623180111 WILLIAN TASK 6 210623191226 JONAS TASK 7 230623151100 JONAS SHEET 1 - TASK GENERATED BY Google FORM, Which we can report as...

Hi everyone, I'm aiming for a formula to split out the minimum option based off numerous conditions; although instead of returning the numerical value I want to know the corresponding value in a text based column. I'm thinking a combination of index/match/min/arrayformula/xlookup is required...

Hi, I am trying to calculate the maximum sum of values within an hour in column. Because the number of values within an hour isn't constant (see my example below) I'm having a lot of trouble trying to get this to work. As you can see in the sheet below, the times are in column A and the value...

Hey I am tracking when people post their food to a spreadsheet. But I am only looking to include unique dates. As new posts are being added daily I went with an arrayformula to save the manual juggling. The only problem is the countuniqueifs formula does not seem to work in an arrayformula...

Greetings, I have a worksheet with 737 columns; A1:ABI64 - below is a small representation of it. In cells B11:B18 I want to add a formula to reference the person in column A and count the 0's for that person, in the range A1:ABI64. What is the best way of doing this please? I have tried...

I have question is it possible to combine a normal formula and array formula because as of my knowledge one only requires enter and another ctrl shift and enter. How do I combine them both if it is possible? =IF(IFNA(VLOOKUP(M2,CHOOSE({1,2},IF('[Tier Price List.xlsx...

Dear Demigods, Hope that I can post this here..... I'm using Google Sheets and I have the following formula in column O which is copied down as far as the eye can see: =ARRAYFORMULA(SUMPRODUCT((GOODSOUT!$A$2:GOODSOUT!$A$1004=GOODSIN!C2)*(GOODSOUT!$L$2:GOODSOUT!$L$1004))) Google Sheets has a...

I am using Google Sheets: I am trying to nest a formula that outputs a reference cell address based on the cells location within an array, and I wish I could just use the indirect function, but I know that the indirect function breaks when nested within the arrayformula. Here's my formula...

I have a spreadsheet that needs to do X, Y, or Z (which are all various sums of columns of numbers) depending upon two variables: If A=5 and B<>0, then do X. If A=6 and B=0, then do Y. Else, do Z. I have a formula that works correctly without using ArrayFormula: =int(if(and(E2="5 courses each...

Assume the following: Cell A1 = 20 Cell A2 = 35 Cell A3 = 45 =SUM(A1:A3) = 100 But I want to have those 3 values within one single cell as an array like A4 ={"20";"35";"45"} and do = SUM(A4). However, when I do this, the result = 20. When I read the cell value of A4 with VBA, Range(A4).Value...

Hi guys, First time posting, and hopefully an easy solution. I have a table called "Workbank" which is 33 columns, but will have a variable number of rows. One of the columns is comprised of alphanumeric strings such as: aa-###, aaa-##, aaaa-#. I would like to store these column values to a...

Hi! I've using cycle macros for searching in each column certain value and then comparing values in adjacent columns in order to get values to show or similar matters At this moment I found out that there is a way to call formulas from vba code via Application.WorksheetFormulas and at the same...

I am trying to use the following Array Formula in my VBA. Range(Cells(2, "AF"), Cells(4, Col)).FormulaArray = "=IF(COLUMNS($AF2:AF2)<=$AE2,INDEX('Demand File '!$A$2:$A$3001,SMALL(IF('Demand File '!$CC$2:$CC$3001=CONCAT($J2,$K2,$L2,""Yes""),ROW('Demand File '!$A$2:$A$3001)-ROW('Demand File...

Hi, I am struggling with the following code, that in essence is trying to copy a range of formulas from one sheet to another: Sub CopySheetToSheet() Dim r, c As Integer Dim rngA As Variant 'HC Reference to Sheet5 Range Alpha Set rngA = Sheet5.Range("Alpha") r = rngA.Rows.Count c =...

Hello, I am trying to figure out how sum up two different functions into one cell. I have figured out the initial formula to sum the functions, I am using an array formula with the sum nested, however, I cannot figure out how to get just the one functions answer to populate if the second...

Hi all, Is it possible to create an array formula in a column that captures data from multiple columns? It is important that an arrayformula is used because I am using an online form that captures data and spits it into a Google spreadsheet. Every time the form captures a new entry, the data is...

Good afternoon, I currently have an array formula in my workbook which basically comes down to this (which does exactly what I expect / want): {SUM(IFERROR(((MONTH(C3:C100)=1)*(YEAR(C3:C100)=2016))*R3:R100,0))} It calculates the sum of a range (column R) when the date (column C) contains...

Hi, i keep getting the above error when trying to run this last bit of my code: Dim laddercount As Double Dim x, y As Double laddercount = Evaluate("COUNTA(R:R)-1") For y = 19 To laddercount + 19 For x = 7 To x + laddercount Cells(x, y).Select...