All Questions
Tagged with google-sheets-formula array-formulas
1,976 questions
1
vote
1
answer
102
views
Generating date range based on allowed weekdays, repeat times and times of day in Google Sheets
Recently, I’ve been dealing with a lot of medical-related things. To make it more manageable, I decided to create a schedule that I could print out, follow, and update as needed.
Input Data
Time of ...
- 13
2
votes
2
answers
59
views
How to create array-based sequential numbering in Google Sheets based on adjacent column, skipping blanks?
In Google Sheets, how can I create an ARRAYFORMULA to generate sequential numbering in column B, only when adjacent C cell is not empty?
I want to start sequential numbering from B10,
If a cell in ...
1
vote
1
answer
87
views
ranking formula start from negative number
How to make a ranking formula but starting from negative numbers and preventing double rankings
SCORE
RANK
-50
1
-25
2
-20
3
-10
5
-20
4
-10
6
-1
7
0
8
3
10
1
9
This is the link.
I tried ...
- 15
0
votes
1
answer
64
views
Google Sheet Vlookup Multiple Criteria
I have a Database sheet with a list of products and each product's details including but not limited to the product size, cost, department and more. On a separate sheet named PriceTiers, I have a list ...
- 69
0
votes
1
answer
54
views
OR function not working with arrayformula in Google Sheets
I want to use an arrayformula function to apply a formula across all rows in my sheet.
However, when adding the following formula:
=ARRAYFORMULA(IF(OR(E2:E="English",E2:E="Spanish")...
- 5
-1
votes
1
answer
58
views
Google Sheets ARRAYFORMULA with Partial Match and Multiple Criteria Not Returning Expected Results
I’m working with a Google Sheets document where I need to look up values based on multiple criteria and perform a partial match search in one of the columns. I’ve been trying to use an ARRAYFORMULA ...
- 13
0
votes
1
answer
45
views
How do I set up a formula that matches columns between two sheets, while returning blank values for the first two rows regardless of values
Background:
I'm using a Google Sheets spreadsheet with four tabs. It uses an AppendRow function to add specific data from Sheet2 onto Sheet3, with the data being exported to other spreadsheets from ...
- 71
1
vote
1
answer
52
views
Arrayformula with Query in Google Sheets
I tried to get ARRAYFORMULA in Google Sheets to automatically fill the cells below with the formula. The following code only works for the first row.
Can I ask for help?
=ArrayFormula( Query(...
- 145
0
votes
1
answer
111
views
ArrayFormula which shows Rows that match multiple columns conditions
I got a Sales Promotions spreadsheet where I must have a Column which shows the Rows of conflicts of same product being promoted in same dates and different sales representatives.
Columns:
E: Retail ...
0
votes
1
answer
61
views
Get the exact value in array
I have a virtual array table as follows. It is generated from query, not from the cell:
Col1
Col2
Col3
A
E
F
Q
B
N
***
***
***
T
Y
I
R
H
J
W
X
M
***
***
***
G
L
K
A
O
P
***
***
***
I ...
- 23
0
votes
1
answer
25
views
Arrayformula with IFS function didn't expand the output result in a column
I used this formula to assign a number of each week in a month using the function (arrayfomula). But the function didn't expand the result for the whole rows. Any suggestion
https://docs.google.com/...
- 131
0
votes
1
answer
60
views
Google Sheets - Difference of Two Columns With Duplicate Values
I am currently having an issue where I need to find the difference between two columns of text values (AT and AV) while maintaining any duplicates. A sample picture of what the two columns may look ...
0
votes
1
answer
59
views
Arrayformula to SUM matching ID AND when date is between two dates
SUMIFS and/or QUERY inside ARRAYFORMULA
The problem explained above is a simpler, but very close approximation of what I'm trying to do. I'm looking to split up the 12 month year into 24 segments, the ...
- 13
0
votes
1
answer
56
views
A data extract by multiple conditions in Google Sheet is working for one raw, but I can't "arrayformula it"
I'm trying to create a table on a sheet, filled with data from multiple tables stacked in another sheet.
A simplified file is here : https://docs.google.com/spreadsheets/d/...
0
votes
1
answer
88
views
Google Sheets - complex search formula - finding a value next to a string to replace conditionally with array formula
I need to check a column of values and assign them the correct type. This is partially achieved as there is another column that has the classifications, but I need to make some corrections. The values ...
- 39
-1
votes
2
answers
51
views
Couple match in google sheet
so I want to match couple in google sheet. Such that...
Here, the XYZ and PQR are a match because both have written their names for each other.
I wrote a formula, but it seems it is incorrect:
=...
0
votes
3
answers
74
views
Sort values in google sheet such that the new column will be a new row
Example, I have table
Name
Value1
Value 2
Value 3
XYZ
V1
V2
ABC
V3
PQR
V5
V12
I need output as:
Name
Value
XYZ
V1
XYZ
V2
ABC
V3
PQR
V5
PQR
V12
SO i tried Transpose in google sheet, but I ...
-1
votes
1
answer
33
views
Sheet formula for finding total score for cell with same name multiply by bias and array
Attached sheet need a formula to array, divide and multiply. Please also include a formula that will auto include more rows when data is added.
Array and multiply
Name
Dance x 3
Jump x 2
Ali
4
8
...
- 61
0
votes
1
answer
54
views
Array function at row2 to fill in the entire row is populating it in the same cell for few formulas. How to resolve this issue?
I am creating a function which filter values from the leads generated and updated in the sheets. Formulas are used in A,E & G using values from other columns already available. It is working for ...
- 3
-1
votes
1
answer
222
views
Apply an arrayformula to a table with multiple rows and columns
In Google Sheets, I'd like to use something like arrayformula() to apply a formula to an entire 2-dimensional array, where the arguments of the formula are taken from the row/column headers.
Example ...
- 222
0
votes
1
answer
46
views
Using arrayformula function does not copy automatically to new rows in Google Sheets
I have a sheet like this:
A B C D
entry profit/loss value balance
300 profit 12 312
I'm going to do this:
I add the balance manually
The value (from the ...
- 4,115
0
votes
1
answer
28
views
Creating Column of seats from a single line description in Google Sheets
we are booking a training and we are trying to convert a description of what seats we have into a columnn list so we can automatically allocate our students to a class.
Eg the the description of the ...
- 6,832
0
votes
1
answer
87
views
Using array formula to join all matches in a cell
I am using google sheets and found a formula that works but I have to drag it down 1000s of rows which is making the sheet super slow/unuseable.
Basically I want to join all matches into a single cell....
0
votes
1
answer
33
views
Force =AND(<vector>,<vector>) to output a <vector> [duplicate]
How can I force a google sheets function like AND to perform its operation individually across each scalar index when it receives two vector inputs?
Seen below, NOT perfoms as expected with the ...
- 603
0
votes
1
answer
313
views
Arrayformula and Sum in Google sheets
How do I make this formula into an ArrayFormula in google sheets..
=IF(A1="online", SUM(B1,C1,D1,E1,F1), B1)
I want the row number to change for each column in formula and then this formula ...
0
votes
1
answer
48
views
SUMIFS + ARRAYFORMULA with multiple criterias
I am trying to create an automatic dividend tracker that will add values from my transaction list (dividends received) in Google Sheets, but I can't seem to get the formula to work completely. I have ...
0
votes
1
answer
69
views
ArrayFormula to give Different Serial Numbering for Different Names
Google Sheet Link
I want to have an Arrayformula at A1 to produce this output:
I want to have a different serial numbering for different names. There can be any number of names, names would not be ...
- 410
-1
votes
1
answer
80
views
Waterfall value (get value from previous row to populate the next one) with arrayformula
I am traying to resolve this but I think my brain is frozen and I cant find a solution online because I dont know how to correctly describe what I want to be able to find the answer.
So, for example I ...
1
vote
1
answer
151
views
ArrayFormula with SUMIFS not Working in Google Sheets
I'm encountering a challenge with an ArrayFormula in Google Sheets that utilizes the SUMIFS function. Here's the scenario:
I have a report with data in columns SpoPartnerShare, SpoMonth, and ...
- 13
0
votes
0
answers
48
views
Issue merging a SUMIFS using index Match into an arrayformula
I am having an issue merging two formulas that, independently work.
Formula 1:
=ARRAYFORMULA(IFERROR( IF(VLOOKUP($A$2:$A&$B$2:$B, {PLANNER!$D:$D&PLANNER!$G:$G, PLANNER!$S:$S}, 2, FALSE) = &...
- 1
1
vote
2
answers
65
views
round sum_range of arrayformula
Here is my table
Column A
Column B
A
1
A
2
B
-1.7148
B
1.3454
B
.3694
I am trying to use a singular formula to sum the totals of Col B based on the Values of Col A rounded to 2 digits
=...
- 103
0
votes
1
answer
42
views
Formula needed for column and row dependency
Tried several times with Query formula, but failed so far.
I have a simple source that looks like this, columns of date, product and quantity.
On another sheet I have a table that has the products on ...
- 441
0
votes
1
answer
172
views
In Google sheets, importing columns from another file using IMPORTRANGE. The inputted columns contain umlauts, causing problems when using ORDER BY
I am using the IMPORTRANGE in Google Sheets combined with the QUERY function to import columns from another file, with ORDER BY. This sorting then needs to be matched with the normal column-sort ...
- 23
1
vote
1
answer
219
views
Google Sheets Array Formula with Multiple Sequences of Varying Lengths, Starting Numbers, and Suffixes?
I've got a unique situation where I'm trying to determine the numbers present in multiple, variable, sequences. Assume each column and row start at A1.
StartNum
NumItems
Suffix
Sequence Formula
E
F
G
...
-1
votes
1
answer
183
views
Google sheets Create a new rows in range of columns based on split of data in one column
I have a sheet that contains transactions with date, type, amount etc. The transaction type may be re-investment, deposit etc. When there is a 're-investment' transaction, I want to create a virtual ...
0
votes
1
answer
117
views
trouble combining array formula and sumproduct (to replace sumif)
I have this 2 google spreadsheet where one of them contain whole data and the other act as a dashboard.
in the dashboard sheet, i would like to show a sum of values in data sheet using this fuction:
=...
- 25
0
votes
1
answer
75
views
How to turn this formula into an arrayformula?
In a google sheet, I have a long list of tickers in column A. In column B, I want to determine for each row, where the previous row was where this ticker was named. For instance, looking at the top of ...
- 4,099
0
votes
1
answer
120
views
How to use Query function to get dates between two dates alongside the corresponding data
I'm trying to convert my data format from something like this:
Original Data Table
To something like this:
Desired Outcome
So the end state is: the dates between the start and end dates in B and C ...
- 3
0
votes
2
answers
54
views
Count unique rows with a criterion of multiple columns
The formula in B2:
=COUNTIF(FILTER(C3:D, C3:C=TRUE, D3:D=TRUE), TRUE)
Should count how many rows have at least one TRUE value in either column C or D
(in the picture the checkboxes in the range C3:C5,...
1
vote
3
answers
228
views
Create a "score" based on the characters contained in a specific cell in Google sheets
We use an in-house scoring system for gauging the quality of our wares by creating a code based on the characteristics of the individual ware, and adding/subtracting points based on the characters ...
- 1,612
-1
votes
1
answer
126
views
Is there a function in Google Sheets that counts the number of times a number appears in the last X entries
Currently I have a formula that shows me the average of the last 3 entries in a sheet. The sheet is a log that has a name and then a number in each column that shows performance per day. Here is the ...
0
votes
1
answer
193
views
Seeking Assistance in Google Sheets: Formulating Functions for Profit Calculation with Multiple Identifiers (Order No.) and Keys (Invoice No.)
Invoice No
CREDIT
A001
290
B001
210
C001
350
A001
250
B001/C001
210
A001
220
Order No
DEBIT
PROFIT
A001
300
10
C001
320
-30
B001/A001
450
-10
A001/B001/C001
490
60
I have two sets of ...
- 53
0
votes
1
answer
185
views
How to use LEFT+FIND combination inside FILTER+ARRAYFORMULA in google sheets
I have the next data:
https://docs.google.com/spreadsheets/d/1b8BmHxUwLjk5DYbff77dd6O82Rl8EBsV3Rr2NCXpMug/edit#gid=2100307022
I need to count some metrics for each row in this table. Also I need to ...
0
votes
1
answer
99
views
Is it possible to combine an Arrayformula with a Transpose?
How can I make the following google sheet calculation using "arrayformula"?
on C2:
=transpose(filter($B$2:$B,$A$2:$A=A2))
on C3:
=transpose(filter($B$2:$B,$A$2:$A=A3))
and so on.
Table
...
0
votes
1
answer
100
views
Create A VLookup Array That Searches Based on multiple criteria and returns a number into a column
I have keyword data that automatically populates into a google sheet tab named 'Keyword Data' segmented by Campaign, ad group, match type and day. Not every row has a conversions.
In another tab there ...
- 13
0
votes
1
answer
58
views
Arrayformula IF and ""AND"
=ARRAYFORMULA({"BONUS";IF(and($H5:$H="SILVER";$J5:$J<$N5:$N);$F5:$F20%;IF(and($H5:$H="GOLD";$J5:$J<$N5:$N);$F5:$F10%;IF(and($H5:$H="PLATINUM";$J5:$J<$...
0
votes
1
answer
63
views
How to insert the ISBLANK into an already valid formula in Google Sheets?
I am trying to add or modify the current formula in Google sheets:
=ARRAYFORMULA(IF('14_Day_Drop'!B2:B=FALSE, "YES", "NO"))
the formula above works exactly the way I want it, BUT......
0
votes
1
answer
48
views
Get a list of dynamically filtered items inside an ARRAYFORMULA in Google Sheets
I am trying to do a pretty simple thing - I have two lists, Countries and Cities.
Each city has a country as a property in a separate column.
I want to list all the cities for each respective country ...
- 129
0
votes
3
answers
302
views
Enumerate different elements of a column
I'm struggling to elaborate an Arrayformula to enumerate the different elements of a column, I have five different type of jobs and I have each job numerated independently. I've prepared a Spreadsheet ...
- 93
-2
votes
3
answers
238
views
SUM Using Multiple Criteria to Determine What is Counted & How [Part 2]
I am trying to create an array formula in Google Sheets to calculate the additional savings resulting from applying a discount pretax.
It is based on a simple calculation but becomes more complex ...
