Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
809
questions
0
votes
1
answer
33
views
Return Value to Different Line with Possible Duplicate Information
In Google Sheets, I need to extract values of D in specific rows and insert them into column I where appropriate.
See link below. Here is an explanation of what I have so far in the spreadsheet:
Raw ...
- 13
2
votes
1
answer
54
views
Append Current Stock Stock Price to Historical Prices
I'm looking to append the current share price of a stock to the end of a period with changing durations.
My current formula for the historical prices is (where cell B3 is a duration of days to ...
- 225
1
vote
1
answer
33
views
How do I build a formula to select a Category based on different select keywords/phrases in a determined column?
I'm hoping to build a formula to automatially select a category based on select keywords/phrases from an imported bank statement.
I attached an editable copy of the Google Sheet I'm looking for help ...
- 13
0
votes
1
answer
21
views
Google Sheets - Array Formula that searches each row and returns a cell value based on conditions
My data contains traffic source data from Google Analytics, grouped by customer. For each row, there is a TRANSPOSE(ARRAYFORMULA( function that fills a horizontal array of values from another sheet. ...
- 3
0
votes
2
answers
35
views
What's wrong with my formula?
In Google Sheets:
First, I'd like to return the values in the whole column of D into Column H if:
E and F equal A and B
C is not blank
This was the formula I tried:
=IF(ISBLANK(C2=TRUE),"",...
- 13
-1
votes
0
answers
8
views
How can I transpose selected row data from one tab to another when 'OK' populates a cell, also needs a static date to be recorded
I am trying to record the number of assets completed each month.
I need to automatically copy/transpose selected cells (or an entire row if easier) from 'Main HUB' tab to 'Numbers Tracker' tab when ...
0
votes
0
answers
30
views
Formula where I can enter an amount and then calculate a cost - HELP! [closed]
Can you help me to make a formula where we can enter a 'coverage amount' and then calculate a 'premium cost'?
I was able to make a trendline here on this graph
if we have a "coverage" field ...
- 15
0
votes
1
answer
17
views
ArrayFormula to transfer items in a column from one google sheet to a column in another google sheet ignoring blank cells
I'm trying to transfer items in a column from one google sheet to a column in another google sheet ignoring blank cells entirely.
I have this formula that will transfer all cells including the blanks:
...
- 1
0
votes
1
answer
20
views
All columns values in one column
I need some help! :S
I have a spreadsheet and three list of items (Column "A", "B" and "C") and I need to put all the values of all the columns in only one column (Column ...
1
vote
1
answer
15
views
Import values from others spreadsheets to a single one
I'm having some trouble to import data values from many spreadsheets to a single one. I need to import all the values from all columns "A" of each spreadsheets to the column "A" of ...
0
votes
2
answers
29
views
Put duplicates values at zero
I'm having some trouble finding a formula to put(replace) my duplicates values into "0". I tried everything I know (not a lot) and nothing works. I have two columns: Column A and Column B. ...
3
votes
1
answer
100
views
How do I check a set of numbers in different columns against numbers in different columns
I am trying to make a Lotto checklist. I have my own numbers in 6 columns A2:F2, and I have typed the winning numbers in H2:M2, 6 columns also. Now I want to check if my numbers in A2:F2 have any one ...
- 55
0
votes
1
answer
20
views
How to get total salary from each month from date?
Given that image below, B is date in format [month/day/year] and salary.
From that date, can we total up salary from specific month in yellow table?
For instance, we want the total number of salary ...
- 101
0
votes
1
answer
26
views
Check What Same Text Is Found In A List & List The Text
I have 2 columns of values. I want to create a formula that looks at Column A and finds what Column B text values are within in the Column A items. Then I want what matches to list in column C. I've ...
0
votes
2
answers
33
views
Google Sheets: Arrays: using ARRAYFORMULA() v. { }
Why does this work: =ARRAYFORMULA(LEFT(A2:A13,1)),
but this doesn't: ={LEFT(A2:A13,1)}
ie, using ARRAYFORMULA returns the array: J
F
M
A
M
J
J
A
S
O
N
D, while using the braces instead of the formula, ...
- 21
0
votes
1
answer
23
views
Formula to randomly select the name associated with lowest value from data set & a random from the lowest options if tied with drop-down select filter
I have a data set on Google Sheets where for example,
Name
Scores
Region
State
First
Row
Row
Row
Second
Row
Row
Row
On another tab, I have a drop-down list to select a specific Region and State. I ...
- 3
0
votes
0
answers
22
views
Array formula & concatenate
i want to make 6 number id code with array formula by combining the job code and the number list... 2 first number is the job code and 4 last number is the number list..
I tried to divide the job code ...
0
votes
2
answers
80
views
Can you do an importrange on data from multiple sheets if the data itself has an arrayformula with a textjoin?
I'm trying to summarize a bunch of sheets into one via a query([(importrange)(importrange)(etc.)}. I've done this multiple times without issue, but now the data I'm pulling is has a columns that is ...
- 1
0
votes
1
answer
38
views
Use MATCH & LARGE but with 2 identical highest values in Google Sheets
Thank you to doubleunary for quickly solving my first of two questions. I am now able to create a single cell that properly lists all of the top 5 largest value column titles separated by commas; even ...
0
votes
1
answer
19
views
Google Sheet >> Trying to count in sequential
I have a set of data, which the value might be repeated and I am attempting to count and mark the counting in sequence.
I am currently trying to create a list using arrayformula (which is a must for ...
1
vote
2
answers
54
views
Google sheets - what function can I use to expand MAXIFS function to an entire column?
I'm trying to make the current column (most recent interaction date with client) display the max values (most recent dates) from ContactLog!b:b (dates of all recorded interactions), when the client ...
- 23
1
vote
1
answer
22
views
simplify redundant information
Is there a way to have the formula eliminate the redundancy. Can i specify the set of unique cells in a grouping and have the redundant part simplify to one line and have it repeat to all the unique ...
- 95
1
vote
1
answer
48
views
merge data of columns that have the same label vertically in google sheets
I have a couple of datasets with the same columns entered horizontally next to each other.
I have a data / facebook leads / facebook adspend column for thee moonth of january in column a, b, c and the ...
- 35
1
vote
1
answer
31
views
Match taking into account characters like ä ë ï ö ü, á, é, í, ó, ú
Hey I am running against a small problem that I do not know the answer of.
I have 2 lists:
List 1 has all my data, for example
Märio
Toad
Bowser
Pëach
Luigi
List 2 has data too (but this list doesnt ...
0
votes
1
answer
114
views
ARRAYFORMULA - Return of Text if cell is not blank and DATE earlier than TODAY
After some unsuccessfull workings hours, I definitely need your support.
I am trying to create an ARRAYFORMULA, so that new rows can be added manually with the wished formulas already implemented in ...
- 1
0
votes
1
answer
18
views
Return Average of a column if
Looking to return average if criteria is met, but I can't get the formula to work when one of my criteria exists in multiple columns. Does anyone have an efficient solution?
=AVERAGEIFS(Sheet2!D:D,...
0
votes
1
answer
27
views
Keeping row content bound in Google Sheets
I have three main sheets in my spreadsheet. The first is a collection if items with relevant details for them, the second is a list of people. The third is used to set some basic relations between the ...
- 204
0
votes
1
answer
9
views
How to write an ARRAYFORMULA which removes values with brackets [ ]?
I am trying to write a formula that pulls all data in column A but removes those that contain brackets to hopefully output to look like picture 2. Any help would be much appreciated :)
- 21
0
votes
1
answer
18
views
How to build a table of imported data from conditional data sources
I have many spreadsheets where data will be added manually and which are all identical. Let's call these UserInputSpreadsheets.
Inside each user spreadsheet are tabs with data which are also identical....
- 101
1
vote
1
answer
11
views
Remove 0g value from when pulling data from a column
Looking for help on writing a formula to pull all data from a column AC (picture 1), removing 0g values and move that data into a new column with no spaces which would look like picture 2.
Thanks in ...
- 21
0
votes
1
answer
50
views
TRANSFORM ROW WISE DATA INTO ONE COLUMN CONSECUTIVELY based on the number of values in each row
I've learned how to use filters to organize row-wise data into columns and am taking that to heart in this spreadsheet! But I've hit a major snag.
If you look at the example I've attached here, you'...
- 5
0
votes
2
answers
35
views
Google Sheets: FIND multiple cells containing specific text
I have a Google Sheets table and one of the columns has email addresses.
I'm filtering that column using this custom formula that works:
=FIND("@gmail.com",I2)
But I need to be able to ...
0
votes
0
answers
31
views
How can I combine data from 200 different google spreadsheets to one master google sheet
I have about 200 google spreadsheets. Each of these google spreadsheet has multiple sheets. I need to gather the data of one of the sheet in all the 200 google sheets to one combine sheets. it is like ...
- 1
1
vote
1
answer
44
views
Lookup and Conditional Formatting
I have some columns in sheet 1 (here in image O and P columns), and in another sheet (here in image R, S, T, U, V, W). I want to check the matching Date and Number from sheet 1 with Date and Number ...
0
votes
1
answer
25
views
Google Sheets - Sum Filter of Table 1 in each row of Table 2
I have table of transactions with categories (let's say 1-5) and assigned dollar values, and another table with a list of each category. I want to sum up all the matching categories from Table 1 with ...
- 25
1
vote
1
answer
23
views
Help with a formula in my working schedule
Background info:
I have a work schedule i need help with. I made a TEST sheet that represents the real sheet. at this time I have no way of rebuilding the sheet "from the ground".
There's a ...
- 11
0
votes
1
answer
22
views
How to use data validation based on 2D array or row and column of tickboxes in google sheets?
Here, I'm attaching the part of the sheet which contains the tickboxes.
Objective is to format the column A with green or red background colour.
Rules:
Data in column A should be green, if any of ...
0
votes
1
answer
71
views
ANY ARRAYFORMULA INSTEAD OF SUMIF/SUMIFS
Hope you're doing well.
My problem is, I need to SUM all BAL for every ROW in Column B.
At this moment I'm currently using =IF(SUMIF(C$1:W$1,"=BAL",C2:W2)<>0,SUMIF(C$1:W$1,"=BAL&...
0
votes
2
answers
31
views
Get summarised data
I have a table
Name
Salary
Desc2
Desc3
ABC
9
X
X
ABC
9
X
X
ABC
9
X
X
XYZ
4
Y
Y
PQR
1
Z
Z
PQR
1
Z
Z
PQR
1
Z
Z
I want it something like :-
Name
Salary
Desc2
Desc3
ABC
27
X
X
XYZ
4
Y
Y
PQR
3
...
2
votes
2
answers
82
views
Can I avoid copying this formula in new rows by using ARRAYFORMULA
Suppose I have this sheet showing which group (1, 2 or 3) each student joined on each day of class. Column B indicates in which group he/she last showed up, which is obtained by using the following ...
- 41
0
votes
2
answers
22
views
How to calculate the remaining working days in a month
I have =INT(B8-NOW())currently running in a cell to show me the remaining days in the month (B8 being a date)
I want to know if there is a way that the output number can calculate only working days, ...
1
vote
1
answer
23
views
Formula to "Autofill" from a list on a linked sheet
I would like a field to auto-populate when I select data from another field. I understand I could do this with an "IF" function.
However, the data from the initiating field is a long list ...
- 35
-1
votes
2
answers
76
views
How to structure a vlookup in an arrayformula to accept multiple columns?
I have been working on this problem for a week, where I was looking for the totals of specific row items in a range multiplied by a factor. The formula below achieves the desired outcome. I need help ...
2
votes
0
answers
22
views
How to link 2 sheets, so when a cell on sheet1 is modified, sheet2 got updated automatically and viceversa
I am trying to have the same updated values in two differents sheets of google spreadsheet.
So when I edit sheet1, the the values in sheet2 got updated. Also the opposite: when I edit something in ...
1
vote
1
answer
23
views
How to tell if a given cell is in an array range in Google Sheets
Some formulas (ARRAYFORMULA, QUERY) can yield values that "spill over" into neighboring cells, as described here: https://support.google.com/docs/answer/6208276?hl=en.
When you select the ...
- 111
0
votes
1
answer
26
views
How to pick a custom columns within an array formula
I have next array formula that combines columns from multiple sheets:
=arrayformula(
{
Data1!A2:F,
iferror( vlookup(Data1!A2:A, Data2!A2:C, {2,3}, false) ),
iferror( vlookup(Data1!A2:...
- 159
0
votes
2
answers
68
views
Array Formula Countif Checkboxes
I'm working on a spreadsheet (in google sheets) for tracking characters' magical abilities.
In column b I want to have a count of how many boxes are ticked. Right now it's just the same formula =...
- 3
0
votes
1
answer
29
views
Formula always return one value for each cell
I was querying a hyperlink row from one sheet to another using this formula:
=QUERY(Data!A2:$F, "SELECT E WHERE C = 'fragment' ")
However I discovered that it copying only text but not the ...
- 159
0
votes
1
answer
21
views
How to replace multiple formulas with single one
I have this formula that I am duplicating for each column within a row:
=HYPERLINK(CONCATENATE("http://mysite.com", B2), "url")
I am wondering is there anyway to replace that ...
- 159
1
vote
1
answer
30
views
Joining multiple sheets horizontally
I have a number of spreadsheets
I want to join them horizontally by Id.
How would you achieve that?
p.s I should mention that the id order could be different in each spreadsheet
- 159