Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
802
questions
0
votes
1
answer
6
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
27
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
16
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
35
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
25
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
17
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
25
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
20
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 ...
- 73
1
vote
1
answer
43
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
29
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
32
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
26
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
16
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
40
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
29
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
26
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
35
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
19
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
22
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
18
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
40
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
30
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
80
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
75
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
22
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
24
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
45
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
25
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
0
votes
1
answer
11
views
How to indicate the source sheet when merging multiple sheets into one
There is a lot of tutorials that show how to merge multiple sheets, so basically there is 2 formulas:
={FILTER({sheet#1-range},LEN({sheet#1-range-first-column})>0);
FILTER({sheet#2-range},LEN({...
- 159
1
vote
1
answer
54
views
Add a constant number to google sheets query result
how do i get this result? i want to get the number one in every row
- 11
1
vote
2
answers
46
views
In Google Sheets, can you repeatedly autofill until next non-blank cell all at once? (previous value)
Basically, what I want to do is fill all of the blank cell in a column with the value of the previous non-blank cells. For example:
I was wondering if there was a way to do this without having to do ...
- 13
1
vote
1
answer
29
views
How to retrieve today and tomorrow's values in a specific tab of a dynamic calendar
Employees are selected in their corresponding shift on each month tab in a dynamic calendar
See 092022 tab in my example:
I want Pemanence tab will be able to retrieve automatically the values in the ...
- 53
1
vote
1
answer
44
views
Combine different sheets with importrange for dynamic data from google forms
I have 4 identical google sheets that get continuously filled with data from 4 different google forms. I want to merge the 4 resulting sheets into one Final Sheet. Moreover I want to be able to ...
- 11
1
vote
1
answer
18
views
Google Sheets Remove Inconsistent Characters
I would like a formula that removes the ending of each of the text so it's consistent
https://docs.google.com/spreadsheets/d/1ysjz4B6Eh4eE5y_cYg76_CilkUena6iQDZtIIMhXSOs/edit?usp=sharing
- 87
1
vote
1
answer
133
views
Split, Flatten, and Repeat Cells in Certain Columns Within an Array Formula for Google Sheets
I am looking for a formula in google sheets to to break down an existing data set at once in a format that's a little more versatile. Here is the link to the sample sheet which is also the source of ...
- 11
3
votes
2
answers
71
views
How to select range into a cell based on drop down selection
I have two sheets in my Google spreadsheet: Meal Plan and Recipes.
Meal Plan looks like this:
Recipes looks like this:
I would like to write a formula or function which allows me to select a meal ...
- 185
1
vote
1
answer
55
views
Google Sheets - How can I return more than one match result from the same search key?
Link to sheet used: Testing Sheet
Thanks to another answer I was able to match and return a single word from a search_key being compared to a specific range using the following formula:
=UNIQUE(...
- 11
1
vote
1
answer
15
views
i'm not sure what function I want for this
I have a column that holds the month number(1....60) I want in another column to indicate what the number "1" is (in this case January 2023) I need a formula to look at a column, if the ...
1
vote
0
answers
13
views
Add more conditions to the formula
=query({IMPORTRANGE("1c6ihzJjzD_Nhbz5rB1s0485PS98V-Q4bfVAiIon5vSA","MC2!A11:S5000")},
"select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, ...
- 11
1
vote
1
answer
218
views
Use of COUNTIF in an array?
Overview
In Google Drive I have a sheet in which I frequently export my Spotify playlists. From this list I like to document artists I listen to.
In short:
Column A holds all the artists occurring in ...
- 23