array formula

So I use the same database, the same cell, same formula. But somehow I get different behavior of formula when I evaluate it. Here I use SEARCH as a single formula {=SEARCH(List,E521)} And I get only first item from the List (table). But here I use SEARCH in combination of formula...

Hi all and thanks in advance... I have the following array formula which is working as intended =SUMPRODUCT(((CONTACTS!$A$2:$A$5000=A2))/COUNTIFS(CONTACTS!$A$2:$A$5000,CONTACTS!$A$2:$A$5000&"",CONTACTS!$R$2:$R$5000,CONTACTS!$R$2:$R$5000&"")) Problem is it takes way to long to calculate. Is...

I have the below array formula in a spreadsheet that i am currently updating so that most of the data is stored in VBA Arrays rather than in cells. I'm having trouble with this array formula. Rather than having the source data in D4:LasTRow2,LCol2 it is now in a 2d array called SA_Corr which...

I was asked to help my boyfriend on a vlookup with multiple conditions. I know that requires using Index Match and using them as an array formula. However when he went to hit "enter" he didn't want to use "ctrl, shift, enter" as I told him but it still ended up working. At work today I...

I've built a timesheet with 15-minute increments. Users enter their task and what project the task is part of, and a single project can have multiple tasks. All projects are in one column and all tasks are in a neighboring column. Total time is listed beside them. The range is D2:G166 (NOTE...

Hello Group, I am working on a project and need one last piece to wrap up this file. Explanation: I have a block of Data B2:N15, There are various formulas within the block that link to other sheets and some manual entry fields. I have set up these blocks so that a user can copy a block and...

I have a spreadsheet that I use to track time logs against projects, for my entire team. I track this on a weekly basis. Unfortunately, the tool we use doesn't give me the breakdown of change each week. It just gives me the total time spent against the project. Given this, each week I export...

Hello. In Excel Office 365, to create an Array formula- "Ctrl-Shift-Enter" is not making the formula work rather it produces the error code #VALUE ! - here is the formula- thank you in advance for your help on this...

Hi, I have a table called BadSyntaxTbl with a column called Bad Syntax. It looks like this: Bad Syntax <!-- b-->< /b> % { I want to apply conditional formatting to a range of cells. The formatting would apply when any of the terms in Bad Syntax column are found anywhere in the formatted cell...

I'm not entirely sure how to explain this issue, so apologies if the title is confusing or misleading. I have a spreadsheet with a fairly large number of array formulas. Maybe there is a more efficient way of doing it but this is where I'm at for the moment. The formulas were working. However...

I have 2 ranges with different number of rows but same number of columns, for example: Range1 = A1:Z10 =10 rows x 26 columns Range2 = A11:Z30 = 20 rows x 26 columns Now I need an array formula of 20 rows x 10 columns, which gives the sum product of every combination of rows from these two...

My "Clean Up" has this: <code> Case "Working_Dispatch" lr58 = wsClear.Cells(Rows.Count, "D").End(xlUp).Row Set clRange = wsClear.Range("A2:U" & lr58 + 2) clRange.FillUp 'autofill upwards to cleanse, keep formatting </code> A formula example I am using [array] is here...

Hello all! This is my first post but I've been following this forum a solid 2-4 months in my journeys learning Excel and building dashboards. The resources present on this website have been of immense assistance. I've ran into quite a roadblock on my current project and was wondering if you...

example In Time 8:00 AM --A Out Time 3:00 PM --B formula 24*-(A-B) Answer 7.00 how do I convert this number into hours and minutes.

Good afternoon, I am trying to apply a tax bracket in my model, and wanted to use a simple formula to calculate my tax expense, I have a table displayed as follows <tbody> A B C 1 CIT rate Maximum Profit Profit 2 0 3 22.5% 1,500 36,500 4 25.5% 7,500 5 27.5% 35,000 6 31.5%...

Hello, I need help with a complex (to me) IF statement. Here is the link to the sheet I am working on: https://docs.google.com/spreadsheets/d/1RbJJknpUzxzi4eGcju7nD0N_X9wfb69uCpunZQxayC4/edit?usp=sharing What I need is on Friday Poles A13 - I need a formula to check Friday Poles D13:F13...

please help me: =([@ColumnA]=[ColumnA])*([@ColumnB]=[ColumnB]) F9 {0:0:0:0:0:1:1:0:0:0:1:0} How to make an array formula to get the row number of the last of "1"?

Hi, I posted an array formula question on the 2nd of Nov at 09:17, which was answered, but frustrating only raised more questions. Having done a bit more reading-up on array formulae, I thought I would try and answer them myself. The problem I have is with the following formula...

In Excel, there is a nifty sort feature you can use to sort by several columns of information. For example, if your data looks like this: <colgroup><col><col></colgroup><tbody> Name Type apple tree ginger root pear tree grape vine apricot tree carrot root banana tree muscadine...

=IF(ROWS(B$10:B10)>$C$2,"",INDEX(Task2.0!A$2:A$3500,SMALL(IF(Task2.0!$A$2:$A$3500=$B$2,IF(Task2.0!$H$2:$H$3500<=$E$2,IF(Task2.0!$H$2:$H$3500>=$D$2,ROW(Task2.0!$H$2:$H$3500)-ROW(Task2.0!$H$2)+1)),ROWS(B$10:B10)))) Can someone tell me what I am missing in the array formula above? I am receiving...