Stack Overflow
Stack Overflow

Questions tagged [array-formulas]

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

3140 questions
174
votes
17 answers

Apply formula to the entire column

I'm changing all the zip codes 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 not feasible to drag the…
skydv
  • 1,789
  • 3
  • 12
  • 7
113
votes
24 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
  • 27,014
  • 16
  • 68
  • 99
70
votes
4 answers

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 6 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…
Hossein
  • 720
  • 1
  • 5
  • 7
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,555
  • 9
  • 54
  • 72
43
votes
4 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
  • 535
  • 1
  • 4
  • 5
38
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
  • 2,246
  • 3
  • 33
  • 29
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
  • 769
  • 1
  • 13
  • 29
22
votes
2 answers

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
  • 979
  • 4
  • 24
  • 40
22
votes
6 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…
TechGeek
  • 2,172
  • 15
  • 42
  • 69
19
votes
5 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
  • 799
  • 4
  • 8
  • 26
18
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
  • 4,774
  • 4
  • 29
  • 46
17
votes
2 answers

How to insert hyperlink to a cell in Google sheet using formula?

I am trying to insert a hyperlink to a cell in a fashion that can be replicated using '=MATCH()" function. However, I can't seem to figure out a method to link a cell in Google sheets without using the GID. When I right-click and "Get link to this…
UK97
  • 218
  • 1
  • 4
  • 12
17
votes
4 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
  • 715
  • 1
  • 6
  • 13
17
votes
7 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
  • 477
  • 1
  • 5
  • 13
15
votes
3 answers

ultimate short custom number formatting - K, M, B, T, etc., Q, D, Googol

is there a way how to custom format ridiculously large numbers (at least up to 10^100 in both ways) in google sheets: thousands > K millions > M billions > B trillions > T etc... negative…
player0
  • 124,011
  • 12
  • 67
  • 124
1
2 3
…
99 100