Stack Overflow
Stack Overflow

Questions tagged [array-formulas]

A formula that works with an array as opposed to single data values

2576 questions
111
votes
14 answers

Apply formula to the entire column

I'm trying to recode all the zip code from Column A into Column B with the formula: =TEXT(A1,"00000") Like this: I want every cell in Column B to be transformed using the formula above. But I have over 40,000 rows, so it is impossible to drag…
skydv
  • 1,159
  • 2
  • 8
  • 7
108
votes
23 answers

Selecting the last value of a column

I have a spreadsheet with some values in column G. Some cells are empty in between, and I need to get the last value from that column into another cell. Something like: =LAST(G2:G9999) except that LAST isn't a function.
cambraca
  • 24,336
  • 15
  • 61
  • 93
48
votes
6 answers

Counting number of occurrences in column?

What would be a good approach to calculate the number of occurrences in a spreadsheet column? Can this be done with a single array formula? Example (column A is input, columns B and C are to be auto-generated): | A | B | C …
feklee
  • 7,203
  • 9
  • 48
  • 67
39
votes
1 answer

ArrayFormula and "AND" Formula in Google Sheets

In Google Sheets, when using ArrayFormula with AND formula, I don't get the results as it should be. A|B 2|7 In C1 I put formula as: =and(A1>5,B1>6) then I get True. If in D1 I put formula as: =ArrayFormula(and(A1:A>5,B1:B>6)) I get the results as…
Hossein
  • 411
  • 1
  • 4
  • 5
32
votes
2 answers

Make Google Spreadsheet Formula Repeat Infinitely

Okay so I have a Google Form that dumps info into a spreadsheet. On each line I need to have a simple calculation done. The problem is I can't figure out how to get it to repeat a formula on every new line as new lines are added. Yes I know how to…
slister
  • 741
  • 1
  • 12
  • 24
31
votes
6 answers

Extract digits from string - Google spreadsheet

In Google spreadsheets, I need a formula to extract all digits (0 to 9) contained into an arbitrary string, that might contain any possible character and put them into a single cell. Examples (Input -> Output) d32Ελληνικάfe9j.r/3-fF66 ->…
thanos.a
  • 1,347
  • 1
  • 20
  • 24
26
votes
3 answers

How do I combine COUNTIF with OR

In Google Spreadsheets, I need to use the COUNTIF function on a range with multiple criteria. So in the table below, I would need to have something like =COUNTIF(B:B,"Mammal"or"Bird") and return a value of 4. A |B ------------------- Animal…
VivaNOLA
  • 365
  • 1
  • 3
  • 5
22
votes
5 answers

Count Unique values with a condition

In column A I have list of different names. In column B, I have values either 0 or 1. I want to get a count of all the unique names from column A which have 1 in column B. Using below array formula I am able count unique names but not able to apply…
Tejas
  • 2,052
  • 13
  • 40
  • 67
16
votes
1 answer

Insert an Array Formula via VBA

I'm using VBA, and I need to insert an array formula (the one that if I'm writing it manually, I'll press Ctrl+Shift+Enter and not just Enter). When I'm inserting it like a regular formula it doesn't work, neither when I put it with {} around…
Bramat
  • 749
  • 3
  • 20
  • 37
16
votes
5 answers

ARRAYFORMULA() does not work with SPLIT()

Why doesn't the split formula get expanded over the entire column when I use =arrayformula(split(input!G2:G, ",")) ? I get result only for the input!G2 cell, but not the rest in the G column. Other formulas like =arrayformula(find(",", input!G2:G))…
jakub
  • 3,941
  • 3
  • 23
  • 39
15
votes
6 answers

Array formula on Excel for Mac

I need to call LINEST in Excel 2011 for Mac, I would say - this was a detective story. My laziness, my curiosity, all was here! I had a homework from university course to use LINEST. I even stopped trying to do so on MAC, because after googling I…
Ievgenii
  • 439
  • 1
  • 4
  • 13
14
votes
2 answers

Google Sheets ArrayFormula with Sumifs

Usually don't need help with sheets but I think my brain is imploding from thinking on this too much. Trying to fill an entire column with an array formula that sums values from a separate column based on conditions from two other columns. If that…
Catu
  • 555
  • 1
  • 4
  • 12
13
votes
4 answers

Using MIN() inside ARRAYFORMULA()

I've seen some examples of using SUM() inside an ARRAYFORMULA() in Google Spreadsheets (and oddly enough, they all seem like workarounds) but I can't figure out how to apply them to using MIN() instead. Let's say I have columns A, B and C and I just…
lima
  • 720
  • 3
  • 8
  • 25
11
votes
5 answers

Convert an Array Formula's Text Results into a Usable Format

When the results of an Array Formula are numbers, I find it generally easy to find an appropriate method to collapse the array into a single result. However when the results of an Array Formula are text, I find it difficult to manipulate the formula…
Grade 'Eh' Bacon
  • 3,650
  • 4
  • 19
  • 44
10
votes
7 answers

Have formula treat value as text, not numeric

I have an Excel formula reading data from a column. The data in that column is sometimes a date-like format, such as "10-11". Despite the fact that I've ensured that column is text formatted -- and all values display correctly as plain text, not…
lcdservices
  • 801
  • 1
  • 8
  • 20
1
2 3
…
99 100