array formula

Hello lucky people, I have a Friday Afternoon style question... I have panels, which are linked to frames. Different panels are linked to different frames. I need to find the overall frame heights, by summing all panel heights which are linked to that frame. I had it all working when the...

Here's my formula: =IF((INDIRECT($C$6)+1/24)>=0,IF((INDIRECT($C$6)+1/24)<(8/24),SUMIF(INDIRECT(B$6),B10,INDIRECT(D$6)),0),0) C6: 'Sheet1'!$C$3:$C$10 (Sheet1 is data entry and column C is time, in 24-hour format) B6: 'Sheet1'!$B$3:$B$10 (Sheet1 is data entry and column B is the Date) D6...

I have a worksheet that pulls a list of data from another sheet and I was able to create the formula so repeating data does not reoccur on my list. My problem now is that I need to edit this formula further to only include data for a certain date range. Here is an example of the data sheet...

I must be doing something wrong...but I came through all the manuals on the web and I am doing it exactly the same how it should work. I have a table. And for each person (name) and each level, I want to get the maximum value from the column "number". So if I select the whole range in column...

I have seen a few articles here about copying an Array Formula using a macro, but none for copying multiple array formulas. I have a part populated sheet where the number of rows can vary. Columns A:J are populated. Cells K6:AT6 contain a mixture of Formulas and Array Formulas. I need to be...

Hi I have following formula which works fine. I would like to add a further criteria to it: =INDEX($C$3:$C$12, SMALL(IF(($B$3:$B$12<=TODAY()+7), ROW($B$3:$B$12)-MIN(ROW($B$3:$B$12))+1, ""), ROW(A1))) AND($C$3:$C$12<>"Complete") Many thanks

Hi Guys! I am having an issue where my array formula is to long for the vba. I know that the limit is 255, but mine is 284+. I dont know how I can shrink it or parse it. Any help would be greatly appreciated. the formula is...

Hi! I have the following situation: In a large table I have conditions in column A and values in column B. I want to create a list of those values in B which meet a given condition in column A ("textjoinif"). To do so I basically have TEXTJOIN(", ";TRUE; IF(columnA="example";columnB;"")). Yet...

I'm sure someone else must have run into this issue before me, but so far I can't find the answer. So here's my problem. I've got an array formula that includes a reference to a table: =SUM(IF(MyTable[[#All],[Column72]:[Column72]]=Sheet2!$A2,MyTable[[#All],[Column10]])) If I put this in...

I'm working on a very advance database and need to look at different instances of a column I've simplified it as much as I can using helper columns but still need at least one column of array formulas, but I can't figure out how to write it correctly. I'm not near my computer so this won't be...

I need help with creating an array formula for a spreadsheet I'm developing. The percentages are constants. In the calculated total column I want the sum of 100%*10 + 88%*20 + 68%*29 + 50%*15 + 20%*6 for each line. Any help or guidance will be greatly appreciated. - Fred <tbody> 100% 88%...

Hello All, Lately, I have been trying to get my around the following issues without much success. What I want to do is the following: Column C (sheet1): Based on multiple conditions, I would like to retrieve the number of units per model (sheet 1 column B) that are found in sheet 3 column A...

Hi, Is there anyway of creating conditional formulas where zeros need to be excluded? I want to use CORREL (and similar functions) along with multiple criteria, such as: =CORREL(IF(--("range A"="criterion A")--("range B"="criterion B");"range C");"range D") i.e. I want to correlate two ranges...

Dear Excel gurus, I feel this is possible, but I’m not familiar enough with Excel array formulas and pro uses of SUMPRODUCT function to find how to make it possible. I'm stuck and need your help. Given a dynamic 2D range (result of an OFFSET function), I would like calculate a value for each...

I have an two-dimensional in cell array {0,2;3,0;0,5;6,0;0,0;0,8} and I would need to to get Max value of each column in a single cell. So, I would like to get {6,8} as a response from {=MAX({0,2;3,0;0,5;6,0;0,0;0,8})} instead of just 8. <tbody> 0 2 3 0 0 5 6 0 0 0 0 8 </tbody> In...

I have a list of from and to dates of employment against a list of names in a sheet. I have another set of from and to dates in a second sheet. I need to find out the names of people in the first sheet that were employed during that period; preferable separated by a comma. I have array entered...

Hi All ! How can I make an "array formula" in VBA without "using" an actual "excel fomula" I'm asking because I need the VBA to work regardless of the language of the Excel or of the environment. so although "=SUM(DATEVALUE(A1:A555))" works, (and would work if I used it via VBA in an english...

Hi Everyone, I have a list of asset IDs and service dates. I'd like to find the last service date and the next service date for a specific asset. The way I tried it was to use a formula like this: {=IF(MIN(IF(Lists!$J$11:$J$31 = 'Action Overview'!A2,Lists!$K$11:$K$31))=0,NA()...

Hello, I am trying to find a better way to accomplish this task. I have a template worksheet that is copied multiple times (renames copies "Labor BOE X of N), then a "template table" is copied/pasted to each new sheet multiple times. There are array formulas in the first row of this "template...

<tbody> A B C D E F G H I… 1 Name Year Eng 1 Eng 2 Eng 3 Mat1 Mat2 Mat 3… 2 John 1 34 23 57 97 90 34 3 Sam 1 34 76 87 34 10 90 4 Luke 2 67 98 45 90 34 98 5 Lucy 3 98 67 45 67 86 90 6 May 3 7 54 76 23 94 38 </tbody> Hi, I would like help with a formula to retrieve...