3,309 questions
0
votes
1
answer
69
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
-1
votes
2
answers
119
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!...
1
vote
2
answers
129
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
0
votes
2
answers
94
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
1
vote
2
answers
184
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
2
votes
2
answers
334
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
0
votes
1
answer
53
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
1
vote
1
answer
140
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 (...
- 13
0
votes
0
answers
33
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
1
vote
1
answer
71
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
56
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 =...
- 737
0
votes
2
answers
102
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 ...
-2
votes
2
answers
211
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
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
1
vote
2
answers
201
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
0
votes
2
answers
101
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
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....
-5
votes
1
answer
68
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
0
votes
3
answers
79
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
-1
votes
1
answer
62
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 ...
2
votes
2
answers
238
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
984
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
-2
votes
1
answer
219
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
-2
votes
2
answers
206
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
3
answers
119
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