The array tag has no usage guidance.

**0**

votes

**1**answer

32 views

### Sum multiple items with multiple criteria

Given the following table, let's say I want to sum the New York "ACT" sales in Q2.
The answer would be 59. How do I do that with a formula?

**0**

votes

**1**answer

23 views

### Excel Formula that autofill an array with a list, based on an array

I'm trying to do a formula that within a range makes a list based on info on a specific array.
What I need is an array that aufills this list based on the following conditions:
1)It has to be the ...

**6**

votes

**4**answers

261 views

### Excel INDEX MATCH Checking Multiple Columns

The problem I'm essentially trying to solve is a VLOOKUP that is checking Columns A:E for a value, and returning the value held in Column F should it be found in any of these.
With VLOOKUP not being ...

**1**

vote

**2**answers

37 views

### Split a column interspersed with zeros into separate columns in Excel, while breaking at zeros

I am looking to split a column array interspersed with zeros into separate columns in Excel while breaking at the zeros in the column and ignoring the zeros.
For example, if I have an array ...

**0**

votes

**2**answers

400 views

### Excel array functions — how to count non-blank cells over multiple worksheets?

I have an Excel workbook with numerous worksheets, some of them containing a numeric value in cell A1:
'DATA_1' 'DATA_2' 'DATA_3'
A A A
1 ...

**0**

votes

**1**answer

29 views

### Need to extract multiple matches from a list of values

I am trying to extract data from a worksheet using an array formula but it will only return one (the first it finds) value. The formula I am using is:
=INDEX($C$2:$C$8, SMALL(IF($B12=$B$2:$B$8, ...

**2**

votes

**2**answers

20k views

### List values based on criteria

I have a table on a worksheet called "DealSetup" that looks like this simplified version:
A B
1 John Doe 1
2 Jane Doe 1
3 Mark Doe 2
4 Doug Doe 1
5 Mary Doe 2
I ...

**0**

votes

**1**answer

14 views

### Using an array as search criterea for find command

I'm wondering if it's possible to declare an array of file types to then be searched with via the find command?
So
fileTypes=(*.omf *.jpeg *.xml *.otf)
find . -name ${fileTypes}
Instead of:
find ...

**0**

votes

**1**answer

12 views

### How can you find the index of a word in a list in GNU make?

I want to be able to have multiple lists in make, all the same size, one of which contains filenames and the others containing info related to those files, in the same respective order. I want to be ...

**0**

votes

**2**answers

28 views

### Matrix in a cell and calling values of this matrix

I would like to be able to create an matrix in a cell without referring to other cells.
Let's say in A1 I'd have the matrix
2 3 4
1 2 3
with entering something like ={2,3,4;1,2,3} in A1.
I would ...

**0**

votes

**1**answer

38 views

### Excel, count unique values if criteria - HUGE data set

problem i have 300.000 entries
Colum A = YEAR
Colum B = month
Colum C = customer number
G5 to G13 = criteria
I want to count how many unique customers pr. year - I tried using an array formula - ...

**0**

votes

**1**answer

38 views

### MIN IF Array Function with Multiple 'is like' criteria

I think I have a good one.
I need a MIN-IF (and MAX-IF) on multiple criteria where near matches are required. I know how execute a MIN IF on multiple criteria using the product of many IF statements ...

**1**

vote

**2**answers

6k views

### Is there a way to use a name range as an array criteria for a sum(countif()) formula in Excel 2010

Picture column A full of comments like feedback from a survey question. Now picture column B with a formula which counts occurrances of particular key words within each comment. I am currently using ...

**2**

votes

**1**answer

3k views

### How to return first non-blank cell and if it's already a value before it, return the third non-blank and so on?

How to make a function which would return the first non-blank cell value but if it's defined in the left cells next to it, it would jump 2 cells and look for the next value.
Table:
1 | 2 | 3 | 4 | ...

**1**

vote

**2**answers

39 views

### Retrieve the array indexes from a range

I am facing an issue where I need to input column indexes from 1 to 1220 in the VLOOKUP function
{=SUM(VLOOKUP(A2,sheet1!$A$3:$AC$11, {1,2, 3, 4, 5, 6, ..., 1219, 1220}, TRUE))}
The only solution I ...

**1**

vote

**2**answers

22 views

### Find correspondent row in array

I've a product's list containing each product's weight. I've another table with ranges of weights, and each range has a different storage cost.
I'd like to have a formula that identifies in which ...

**2**

votes

**2**answers

83 views

### How to highlight boundaries of an array in Excel?

I'd like to know if there's a way to make excel show me where are the boundaries of an array, like the highlight I made in the picture below.
Some explanation: I have a worksheet that is full of ...

**2**

votes

**2**answers

111 views

### How to flag rows with the pattern non-blank, blank, non-blank using Excel

I would like your help with something I have been trying to figure out for the past two days.
I have data with 1000's of rows and several columns. I want to insert a column with a (array) formula ...

**2**

votes

**1**answer

68 views

### Trying to create a formula to calculate a rate value per row in Excel

I think this is to do with a formula over an array but I canâ€™t get it to work.
Iâ€™m looking for the easiest method to do the following.
Say I have this setup, columns are 'jobs' and rows are ...

**0**

votes

**2**answers

32 views

### Excel, possible array? Not sure how to calculate conditional steps

I have two rows of numbers, 1 and 2, they go from G-Z. I want to calculate the following:
(G2-G1, if greater than zero) add to...
(H2-H1, if greater than zero) add to...
(I2-I1, if greater than zero) ...

**0**

votes

**0**answers

48 views

### Condensing a Formula checking numerous Countifs across rows and columns

I am trying to find a way to turn the below equation into something less cumbersome. Basically I have an array of Countifs which are checking table data to see if the selected person is taking 10 mg ...

**0**

votes

**0**answers

13 views

### Selecting only flagged observations from one worksheet for calculation on another worksheet

I've inherited my first Excel model and need to get it to calculate faster. As it stands now, there's a dataset on one of the worksheets that is 50,000 x 20, to which an additional 30 or so columns ...

**1**

vote

**1**answer

138 views

### How to count number of cells over multiple worksheets whose values are in between a given range?

I have an Excel workbook with numerous worksheets containing data like:
'DATA_1' 'DATA_2' 'DATA_3'
A B A ...

**0**

votes

**1**answer

721 views

### Add hashes with duplicate keys to array

I have a bunch of hashes in powershell with identical keys. Though creating an array of hashes with different keys is straightforward and thoroughly discussed on the internet, I'm unable to find any ...

**6**

votes

**1**answer

211 views

### Use functions like Countif, Countblank etc. to refer to discontiguous ranges.

Is there a way to use array formulas or some other feature to essentially have an excel function that means.
=Countblank({A1,A4:A6,A112:127,B29:B38}) etc.
Essentially, I want to refer to multiple ...

**0**

votes

**1**answer

21 views

### Trying to show in one cell the date at which a cash flow will become negative

I made a cash flow projections sheet with columns going from D to FE and Rows going from 1 to 65.
On the top of each column I have a row with dates from D8 to FE8.
On the bottom I have a row with ...

**0**

votes

**1**answer

414 views

### Enumerating every other array element in loop

I have a dynamic array with key-pair values and Iâ€™d like to enumerate every other value. In this case, just the names in the array. Since Iâ€™m not using bash v4, I have been able to use seq to step ...

**1**

vote

**1**answer

171 views

### Make an array formula in excel calculate only on active rows

When excel calculates formulas over a column (e.g. a lookup), it's smart enough to only calculate until the last active row, rather than also running on all the blank rows.
With array formulas, it ...

**0**

votes

**1**answer

73 views

### Need unique count of text from multiple columns by category

My data looks something like (assume begins in A1):
Category,Label1,Label2
Dogs,blank,doe
Dogs,zoo,blank
Cats,zoo,yup
Cats,doe,hgg
Cats,blank,doe
What the result should look like:
...

**-1**

votes

**1**answer

53 views

### Filter an Array into another page of an excel spreadheet

I have an excel table that includes one column that lists Y, N, N/A, or Blank. I need my filter to pull the rows that include the N, or Blank and paste them into another page of the spreadsheet.
I ...

**0**

votes

**1**answer

200 views

### Matlab extract numbers from cell with mixed contents

I have a cell array with different datatypes, from an excel sheet that I need to read in, but which is beyond my control.
[~, ~, raw] = xlsread(xlsFile)
raw is now a r-by-c cell vector, for ...

**3**

votes

**2**answers

739 views

### Excel, how to use text functions on lookup array without putting in another column?

If I'm using a vlookup, or a match function but I need to do some mumbo jumbo on my lookup material, is there a way of doing this in the lookup array without simply adding another column?
Let's say I ...

**1**

vote

**1**answer

116 views

### INDEX( SMALL( IF(… resulting in #NUM today, yesterday was correct

{=INDEX(Sheet1!$A:$M,SMALL(IF(Sheet1!$O:$O=TRUE,ROW(Sheet1!$O:$O)),ROW(1:1)),1)}
Yesterday it was full of useful values today there are #NUM errors in every cell. Not sure what went awry? Any ...

**1**

vote

**0**answers

29 views

### Return 'dir' command results into array/list [duplicate]

I want to know how to return the output of the dir command into an array.
I'm using the command like this: dir /s /b *.txt how would I put each result (file name) into an array/list.
Thanks

**0**

votes

**1**answer

75 views

### VBA macro crashes when using variable Ranges instead of fixed ones

I've tried to include comments with the code to make it readable, especially as I'm sure there are better ways of doing this!
I have a table with purchases and sales for a specific product. E.g.
ID ...

**1**

vote

**1**answer

32 views

### Display Associative array in awk

How can I display Associative array, when I don't know index?
My script:
{
tab[$1]=tab[$1]+1
}
END{
print tab[*]#display array -> it doesn't work
}

**0**

votes

**2**answers

5k views

### In Excel I need to find data matches between two arrays - one horizontal and one vertical

In Excel 2010 (or 2007 - I have both, although my OS is only Win7 32 bit as a limitation for some legacy applications we run), I need to find how I can find and return the matching value from two data ...

**0**

votes

**1**answer

54 views

### How do I zero out an SAS hard drive which is part of a Dot Hill array?

I am helping a friend who has a Dot Hill Array (server?) and a full set of SAS drives that go with it.
The end goal is to zero out the drives so they can be re-used or sold. What I don't know... is ...

**0**

votes

**1**answer

37 views

### Excel 2010 Array (CSE) Formula: How to remove a part of contiguous (adjacent) range

I would really appreciate help on the following question:
Is there a way to write a formula that removes a segment from a contiguous range?
For example, say there is a formula ...

**1**

vote

**2**answers

281 views

### Excel 2010 Array Formula (CSE) Dynamic cell range/reference; index? offset?

This might be an easy question to some but I am completely lost so I would really appreciate any help out there!
A
1 10
2 20
3 30
4 40
5 50
6 60
7 70
...

**0**

votes

**4**answers

787 views

### Excel: Sum values on a column based on criteria* on other columns *(complex criteria)

See below for rewrite.
Please refer to the data below for the question. The very top A B C are columns and the far left numbers 1 through 15 are rows. The rest are cells.
A B ...

**-1**

votes

**1**answer

67 views

### Array formulas and Logic, checking against multiple criteria

I'm currently just troubleshooting a report spreadsheet I've written. The logic I had written isn't working, so I thought I'd see if I could ask for some help here.
Essentially, I have a list of ...

**1**

vote

**0**answers

135 views

### Properly extracting from an array

I have a CSV with one column, numbers formatted as 111-0000, that I'm reading in to process one at a time. I'm trying to import the column of information from the CSV, but I'm having trouble figuring ...

**0**

votes

**0**answers

95 views

### SUMPRODUCT & ROUND

Is there a way to get the product of the array with conditions to round to the 2nd decimal place before summing?
Current formula w/o rounding:
...

**1**

vote

**0**answers

46 views

### Where does the calculation take place? [closed]

In Google spreadsheets, I have quite a big amount of relatively complex array formulas in many cells. That takes ages to complete calculating.
Does the speed of calculation depend on my PC ...

**0**

votes

**1**answer

2k views

### How to lookup a value and return multiple unique values (remove duplicates) to a single cell - comma separated

I am trying to take a value (unique id) from column AC, do a lookup in column (B) and return (meaning concatenate) the multiple unique values from an adjacent column (C) to a single cell in column AD. ...

**0**

votes

**1**answer

544 views

### Excel horizontal only sum of an array (matrix)

I have a formula which generates a 2D array result. For example:
{=ROW(4:6)^TRANSPOSE(ROW(1:3))}
(the curly brackets are added by excel when you hit CTRL+SHIFT+ENTER
)
I need to sum only the ...

**0**

votes

**2**answers

141 views

### Excel formula to generate a custom array result

I have a formula in excel which contains an array; {1,2,3} its use can be seen at
I would like to generate it dynamically eg arrayfun(4) would generate {1,2,3,4}.
I can generate a very long array with ...

**0**

votes

**1**answer

460 views

### BASH associative array rearrange keys

I am running into a weird issue with associative arrays in bash.
I have the following files in a directory:
ls -lart
drwxr-xr-x. 3 root root 4096 Feb 9 11:14 ..
-rw-r--r-- 1 root root 3275 Feb 9 ...

**3**

votes

**0**answers

108 views

### How to switch off “you can use new shorter array creation syntax” hint in NetBeans IDE 8.0.2?

I use NetBeans IDE 8.0.2 on Linux Mint.
I searched through the options few times, but I can never find the option to switch this hint off in there.
I know well about the new syntax in PHP, but I ...