Questions tagged [google-sheets-arrayformula]

Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.

Filter by
Sorted by
Tagged with
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 ...
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 ...
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 ...
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 :)
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 ...
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 ...
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 ...
-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:...
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 ...
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 ...
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
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({...
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
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, ...
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 ...

1
2 3 4 5
17