3,309 questions
0
votes
1
answer
79
views
How to use ARRAYFORMULA with multiple criteria XLOOKUP or INDEX MATCH? (SHEETS)
I want to add in some additional information to a sheet that is populated by Google Forms from another tab in the same worksheet.
I want to have a formula which looks up a value based on multiple ...
- 1
0
votes
2
answers
103
views
Sheets Arrayformula: increment range size row by row
I am doing a tiny home store overview lists. At the movement sheet I want to sum ONLY the same and previous rows but never mix the results with the future row values.
As I use mostly ARRAYFORMULAs I ...
-1
votes
2
answers
126
views
How can I combine multiple "=ARRAYFORMULA(SUM(IF(IMPORTRANGE()" formulas that evaluates multiple ranges?
I originally tried a sumifs formula but that did not work so came up with this work around.
=ARRAYFORMULA(SUM(IF(IMPORTRANGE($S$3,"Sheet1!A2:A1009")=$G$3, (IF(IMPORTRANGE($S$3,"Sheet1!...
2
votes
3
answers
169
views
How to extract multiple blocks of rows between two keywords in a Google Sheets column into adjacent columns (formula-only)
I have a single column that contains mixed data (strings, numbers, sometimes blanks) and from time to time, there are marker rows that delimit blocks of data.
What I would like to do is:
Start ...
- 57
1
vote
2
answers
133
views
Convert Dates into 3‑Letter Month for Rows, Year for Columns, and Sum Sales
I have a dataset in Google Sheets like this:
Purchase Date
Price
9/24/2023
$49.00
10/25/2023
$29.00
1/4/2024
$29.00
1/8/2024
$39.00
1/31/2024
$39.00
2/21/2024
$29.00
3/13/2024
$49.00
3/13/2024
$39.00
...
- 611
1
vote
1
answer
89
views
How to split array of strings by a delimiter with more than 1 characters
I have a column of strings of the format "#.# km, # mins" in column F. (The spaces are literal. # is for numeric. The number of #s is not literal.) For example,
F
21.2 km, 27 mins
I ...
- 10.1k
0
votes
2
answers
104
views
Formula to Display Each Multi-Select Value in Google Sheets Dropdown on a Separate Line
I having some difficulty solving the issue below. Would appreciate some help with this.
My Google Spreadsheet has two tabs. Product_Database and Data_Sample. In Data_Sample, I have a column of ...
- 1,070
0
votes
1
answer
81
views
Cell relative to current row in an array formula
I've got a spreadsheet listing items with a possible child-parent relation (no grand children, children never come before their parent, but children of different parents can be mixed), and I'd like to ...
- 7,537
1
vote
2
answers
192
views
Google Sheets Filter on REGEXMATCH AND Condition
I have a spreadsheet with multiple tabs in which I would like to filter all existing rows in one sheet (the Source sheet) based on a set of comma-separated tags.
I have a dropdown at the top of the ...
- 40
-1
votes
2
answers
132
views
How do I write a formula in Google Sheets to find the current period in a fiscal year depending on today's date?
I'm trying to write a formula that determines the fiscal year period based on a July 1 start date. I would like quarter intervals for each period to signify which week of the month for the period as ...
2
votes
2
answers
336
views
How to postpone certain holidays that fall on weekend
I live in a country, where by law, certain holidays are not allowed to fall on a weekend. If they do, they need to be delayed to fall on the first available working day.
For example, the holiday of ...
- 67
3
votes
3
answers
223
views
Is there a formula in Excel to return multiple values in one cell for multiple lookup values in another cell?
I’ve run into this issue a few times now and can’t seem to figure it out. Is there a way to return multiple values in one cell (i.e. cell 2) for multiple lookup criteria in another cell (i.e. cell 1) ...
0
votes
1
answer
54
views
How to use ARRAYFORMULA to count the values of each row
I made a sheet to use as a test checker. Each test covers different domains. I can count all correct answers using SUMPRODUCT, but when I sort items by domain, the formula stops working. What formula ...
- 1
0
votes
1
answer
89
views
Using ARRAYFORMULA and filtering for different columns based on another cell
I have a spreadsheet with multiple tabs. On one, I have where players indicate if they'll be attending a game on a particular date, and on the other I want to populate a list of who has rsvp'd "...
- 13
1
vote
1
answer
151
views
Google Sheets - Array Formula with Xlookup and Importrange
I have a Sheet which basically adds name and address info from a membership list to a list of bank payments.
LINK TO EXAMPLE SHEET
It uses an Xlookup (in Column G) to compare the Bank Account Name (...
- 23
0
votes
2
answers
84
views
Adding SORT or SORTBY into excel function
I am looking to add a sortby or sort function into my query but can't figure out how.
=TAKE(CHOOSECOLS(FILTER(Revenue,(Revenue[Top Product]=$C$66)*(Revenue[Location]=$C$67)),1,2,17),10)
Here is what ...
- 45
0
votes
0
answers
35
views
How do I use the group # to look up value [duplicate]
enter image description here
Group #
Food
Qty
1
Apple
10
1
Banana
33
2
Spinach
28
2
Kale
83
2
Cucumber
11
3
Burger
42
3
French Fries
67
Group
1
Food
100
33
I want to have this formula setup where it ...
- 41
-3
votes
1
answer
92
views
Arrayformula with sumifs and 1 static criteria
Date PRDEF55FA9E PRDDCACBC41 PRDFEC1A0E8
2025-03-12 0 0 0
2025-03-11 20740 22740 8680
2025-03-10 17620 12060 4200
2025-03-08 16380 ...
1
vote
1
answer
75
views
Combining rows and columns where there's a non-zero cell
I have a table in google sheets which is concerned with role/group names. These are sometimes shared, sometimes not.
I need to use only those names which belong to the relevant cloud service - and ...
- 2,248
1
vote
1
answer
78
views
INDEX MATCH Function Inconsistent
I have an excel table (Sales Table) that uses an INDEX MATCH function within a few columns. There are 2 specific columns (Product and Part Number) which are using a material number column to pull ...
- 37
1
vote
1
answer
64
views
Compare which values of array1 are present in array2
I'm trying to compare 2 arrays using this formula
=(A1:A5={"String1","String2"})
I'm getting a spilled array of 2 columns like this
How can I get a unique array? I've tried with =...
- 765
0
votes
4
answers
247
views
How to split comma-separated values across multiple rows dynamically?
I have a table in Google Sheets with products (URLs) in one column and a list of comma-separated colors in another column. I want to transform it into a new table where each product is repeated for ...
- 87
0
votes
2
answers
109
views
Simplify an Excel array function
I'm creating a excel file where I don't want to use VBA which would model the drawdown of loans (drawn each quarter for varying amount), and the reimbursement schedule of these loans (which are all ...
0
votes
1
answer
68
views
How do I get all of the values of the adjacent cell matching a text
Not an excel expert here. I want to get all of the Hours for each person and add them up. Just like the image.J4 suppose to get all Jane adjacent hours then add them up and so on.
I saw index and ...
- 21
-2
votes
2
answers
228
views
Combining array_constrain and arrayformula and getting results in multiple columns
I have a template where there's a guestlist on one tab.
On the print tab, I want to populate it with the guestlist, but not include those who show "refund" or "cancelled".
I've ...
- 13
0
votes
2
answers
89
views
Label the row according to the source
I want to importrange from Spreadsheet OLD with several tabs/sheets into Spreadsheet NEW. What i want to do is to import values from SheetA and Sheet B.
First column: vstack(importrange(SheetA!A5:A),...
- 9
1
vote
3
answers
155
views
Standardize IDs from Multiple Columns into One Column
I have data related to Product Name, Tool Name, and ID Tool with the following data example:
Product Name
Tool Name
ID Tool (Standard)
ID Tool (Alternative 1)
ID Tool (Alternative 2)
Product A
Tool A1
...
- 71
2
votes
2
answers
149
views
Find values of an array based on an interval
I would like to find values in an array that are at least an interval length apart. Lets say I have the following data set
0.1 0.21 0.26 0.3 0.36 0.4 0.52 0.76 0.91 1.2
and my interval is 0.25. I ...
- 149
1
vote
2
answers
210
views
How to create an excel formula using LET and other dynamic array functions which gives an calculated output of each column?
I have a table like this which stores marks of students in different subjects
Name
Class
ID
Math
English
Hindi
Tony
5
1
39
30
30
Andrew
5
2
40
20
20
Mark
5
3
40
10
30
and a tabel like this which sets ...
- 67
1
vote
0
answers
31
views
Is it possible to calculate built in array functions using the Office.js API?
I'm trying to write an addin that takes a formula, parses it and then calculates the value of the formula. This all works for formulas that do not use ranges as we can use the Excel.Functions class to ...
0
votes
2
answers
110
views
List All Workdays between Start and End Dates Displaying Associated Name and Details in Other Columns
I have a spreadsheet that looks like this:
Entry Date
Start
End
Name
Details
9/13/2024 13:46:03
09/13/2024
09/13/2024
James Jameson
Out—Called off-Emergency
9/15/202413:46:45
09/30/2024
10/04/2024
...
- 1,070
0
votes
3
answers
83
views
Running Max in ArrayFormula of a Query
I am generating a set of data in Google Sheets with a query and then want to get a running max of that data:
My query is as follows:
=query(GOOGLEFINANCE("SPX","price",TODAY()-365,...
- 1
0
votes
0
answers
53
views
ArrayFormula is breaking the CopyPaste() funtion. Create new row but empty [duplicate]
I have the copyPaste function active on a google sheet that copies the new rows created on the sheet to another sheet. Everything works regularly.
function copyPaste() {
var ss = SpreadsheetApp....
-1
votes
1
answer
83
views
How to calculate my overtime (pm to am) in Google sheets? [duplicate]
My table
I have a problem when I try to calculate my Overtime and I'm looking for a working formula that can help me.
The formula, I use works for me perfect when I don't work from evening to early ...
-5
votes
1
answer
72
views
ArrayFormula With Header [duplicate]
A sheet that receives imported data has 4 cols. Col A has Datetimes, and in Col D I'd like to use an arrayformula to calc the minutes elapsed from the previous to the current datetime.
I've tried a ...
- 1,964
1
vote
1
answer
137
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
0
votes
3
answers
86
views
How do I get the unique item (remove duplicates) from column A and display pass if all values are pass from column B, otherwise it's a fail
How do I output the unique item (remove duplicates) from column A and output pass if all values are pass and fail if even one value is a fail or blank from column B:
Example:
Column A Column B
jack ...
- 11
0
votes
3
answers
63
views
Google Sheets ARRAYFORMULA to find the submission date of the previous similar report
Based on the data in this sheet, can you help me make a formula to find the data from A of the previous Fire report for the same location?
In other words, I need to find the previous Timestamp of the ...
-1
votes
1
answer
65
views
Counting blank cells by arrayformula
I would like an arrayformula in B1 that calculates for every cell in column B how many empty cells I have in the previous rows in column A, including the current row.
I wasn't able to achieve this ...
-1
votes
2
answers
116
views
Pull data from one Sheet to another Sheet based on the Header names, headers in different order
I can't believe I can't figure this out but I keep getting errors.
Example sheets are found at: https://docs.google.com/spreadsheets/d/1q4qRgXSq6xXhddArPCoBIuOOupRvokUd0andADCNN0Q/edit?usp=sharing
'...
- 67
2
votes
2
answers
253
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 ...
0
votes
1
answer
60
views
Google sheets ArrayFormula with several pages
I made an example - https://docs.google.com/spreadsheets/d/1xRl1vBttWToJeDjJB4Okd6FKidLyvoPF2uXJmLCOzPI/edit?usp=sharing
Formula Used:
=Arrayformula(INDEX(FILTER(POIOS!D2:D;POIOS!B2:B<=B2;POIOS!C2:...
- 39
1
vote
1
answer
1k
views
"Array arguments to COUNTIFS are of different size" - counting the number of rows in Sheets with multiple criteria
I am new to Sheets so this might be a straightforward problem. I have a sheet with editions in various states and types. I am trying to count how many editions were created before a particular date ...
- 21
1
vote
1
answer
119
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 the formula ...
- 13
-2
votes
1
answer
235
views
Sort Via Index Match
I am using this formula in Excel 2016 so it doesn't have the sort function.
=IFERROR(INDEX($F$41:$F$500, MATCH(SMALL(IF(COUNTIF($J$1:J1,$F$41:$F$500)=0,COUNTIF($F$41:$F$500,"<"&$F$41:$...
- 81
0
votes
1
answer
146
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
-2
votes
2
answers
208
views
Google sheets ArrayFormula to create a check register
Google sheets
I am trying to use an arrayformula to add the D cell of my previous row, to the C cell of my current row and place the result in my current row D cell. Basically it is a check registry ...
- 1
0
votes
1
answer
157
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
0
votes
3
answers
122
views
How to count a specific word from a string in a cell from the entire column in google sheets
I have a data in a column like the name of Fruits with comma separate, to which I want to find a count of each fruit occurrence in the cells.
Sample data the entire data is in one column.
Fruits
Apple
...
- 3
-1
votes
1
answer
88
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 ...
- 23