Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,868
questions
0
votes
1
answer
12
views
Google Sheets Auto Categorize Budget from Statement
I have a Categories sheet and a Statement sheet. The Categories sheet contains a list of all possible categories, and the Statement sheet is an export of my credit card. How can I automatically ...
2
votes
3
answers
41
views
Multiply/Concatenate String Arrays
Suppose I have three columns importing data from elsewhere, like so:
Name
Company
Division
Adam
Meta
Ops
Betty
Nvidia
Sales
Carl
OpenAI
Development
The number of values in each column may change ...
0
votes
1
answer
26
views
Absolute cell references not working as I'd expect, what am I doing wrong
I am working on a sheet that tracks yearly budgets to review/see trends in the data. I am trying to make it user friendly and essentially "dummy-proof" for lack of a better word.
Most of the ...
0
votes
2
answers
37
views
How to use a cell's array value
Simple question, but I can't figure it out.
In the spreadsheet above I have a table in A1:B5. I have a filter in A7 and its value is an array.
In A11, I want to textjoin all the values of the filter ...
0
votes
1
answer
11
views
Grouping or linking data I added on a sheet to its corresponding filtered data brought over from another sheet
The small towing company I work for uses Google sheets for our calls logs. I filter logged calls so that all police calls are copied from the "General Log" sheet and show up on a different ...
1
vote
1
answer
24
views
Combine two SUMIF formulas into a single formula
I have two SUMIF formulas. Both do what I expect them to
but I need to combine them together.
The first formula gets all values from Column I where a specific word (eg "Word") is written in ...
0
votes
2
answers
36
views
How do I use google sheets to remove items as they are selected from a list that may include duplicates
I would like column C to be column A with everything in column B filtered out. Column A may have duplicates and those should carry over into column C, unless the same number of that item appears in ...
-1
votes
2
answers
47
views
How to sum across columns automatically?
Would like your help to solve the problem as file attached.
https://docs.google.com/spreadsheets/d/1E3KTOYHuYIJy9uQKsvlXwbCpwL6cU1IHAQ-Yirb-AfQ/edit#gid=0
I would like each column to sum automatically ...
1
vote
2
answers
42
views
Generate formula programmatically
I am trying to generate a large number of formulas programmatically.
I can easily generate a formula text doing something like ="=1+1" (in reality it will be a bit more complex). This will ...
1
vote
1
answer
22
views
IF OR conditions & FILTER in google sheets
This is what I am trying to accomplish:
If column A = "blue", OR column A = "red" AND column B = "sold", get sum of column C
Here are my attempts:
=SUMIFS(or(C:C, A:A, &...
3
votes
1
answer
40
views
What's the difference between using "<>"&"" vs. "<>" in Google Sheets formulas?
For Google Sheets, I've seen people use both =COUNTIF(range,"<>"&"") as well as =COUNTIF(range,"<>") to count the number of non-empty cells in a range.
Is ...
0
votes
1
answer
21
views
Counta and Array_constrain formula in Google Spreadsheets
I am creating a sheet in google sheets to look like a dashboard from the below data. I am asked to create this with formulas. I am getting the filter part correct. But changing the values from filter ...
1
vote
1
answer
19
views
Hide a value in a cell unless another cell has a given value
I'm trying to create a formula to generate a list of values in a dropdown.
Each row contains a task that has a status, and a priority.
The priority is set from a dropdown containing a list of numbers ...
0
votes
2
answers
40
views
Googlel Sheets formula to SUMIFS with multiple AND and OR criteria
I have a dataset similar to the image below. I am trying to calculate the sum in column A if:
Region (G) = NorAm AND Team (H) = Corporate AND Stage 7 (F) <= 2/1/24 where either of these conditions ...
-1
votes
1
answer
39
views
Relative cell reference in formula doesn't change as expected when copied to a different cell
I have a working IMPORTXML formula in Google Sheets that includes a relative cell reference. When I copy the formula to another cell, I expect the relative cell reference to update but it does not.
...
2
votes
1
answer
87
views
Formula Suddenly Stopped Working
I have a Google Sheet which requires time to be submitted for different people. I recently had a SUMIFS on another sheet adding the time together based on today() function. The formula I was using is ...
0
votes
1
answer
26
views
Combine Columns from Another Sheet
I'm very new to using Google Sheets, and I'm trying to create a sheet that pulls data from my form submissions.
The form submissions go into Shift Report and I have two outcomes depending on product. ...
1
vote
1
answer
27
views
Count cells with an integer in them as substring
Most cells in the column have a single integer in them, e.g. 5. Some cells have a range as a string, e.g. '4-5. (Let's ignore the problem where someone types 4-5 and the app interprets it as April 5th,...
1
vote
0
answers
26
views
Formula that will duplicate certain cells (2 ranges of cells) in a row & place in the next row according to a # in a cell from 2-20
It sounds confusing. I am entering futures trades in a log. Sometimes those trades (like one trade) is/are copied multiple times and taken all at once in groups or batches/same trade but several ...
1
vote
1
answer
68
views
Is there a function similar to Excel's "Evaluate Formula" to help debug complex Google Sheets formulas?
In Excel there is a button to "Evaluate Formula", so I could step through whatever formula I wrote and find where I messed up. Is there anything equivalent in Google Sheets?
EDIT:
I am not ...
1
vote
1
answer
27
views
Why is cell that contains TRUE not equal to "TRUE"?
Here's my formula: =AND(M2= "FALSE", N2= "TRUE", O2= "A",P2= "No")
And here's the sheet. My value returned false. What do I do?
1
vote
1
answer
24
views
Formula to check if a form is filled out each week
The spreadsheet we are attempting to construct using Google Sheets takes form responses across multiple weeks (which come from the same form) and checks to see if that form has been completed for that ...
0
votes
1
answer
35
views
How to force reloading custom function result? [duplicate]
I use this script to get the current sheet name :
function sheetName() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}
then use =sheetName() in the cell to load the ...
2
votes
1
answer
42
views
How to set a minimum value for a cell in google sheets?
We use google sheets to do price updates for our websites. Sometimes there may be a typo that allows too low of a value and we want to avoid the possibilty of these kinds of errors. Ideally, looking ...
1
vote
1
answer
15
views
How to include a cell value in a FILTER formula
I want to import data from the main datasheet (Sheet 1) into a person specific sheet based on if their name is in column E or F in sheet 1. Currently using the following formula =FILTER('Sheet1'!B8:D, ...
3
votes
1
answer
78
views
Google sheets and semi-autopopulation of data for attendance of a private school where students come two days a week and get to choose the day
The students in my school are not all scheduled to attend every day.
In Google Sheets I have a sheet, 'student list' that lists the students that attend classes for each given weekday:
The first row ...
1
vote
1
answer
98
views
Track elapsed hours in Google Sheet
I have a table in Google Sheets that I use to track the time elapsed between a TIME IN and a TIME OUT.
As you can see below, my TOTAL formula in D3 works, calculating the elapsed time from 5 AM to 3 ...
0
votes
0
answers
20
views
Need a formula for Google Sheets to pull information from one sheet into another
I want to create a google excel sheet that will populate student names into cells based on what room they have chosen for rewards day. Students will pick the room they will go to in a google form. ...
-2
votes
2
answers
43
views
Can't make my code work with ARRAYFUNCTION
I'm new to this, but I finally got something working that does what I need in Google Sheets, and I got it all into one cell. But it won't work with the ARRAYFUNCTION which is necessary for the sheet I'...
0
votes
1
answer
36
views
Convert US dates to international format in Google Sheets
I have a Google spreadsheet which I use to track my accounts. I need to add Etsy sales to this, and have added a new sheet which is pasted from the CSV supplied by Etsy.
Unfortunately Etsy only ...
0
votes
1
answer
134
views
Google Sheet combine rows with same date
I'm loosing my mind with problem in Google Sheets. We are using Appsheet to insert data for daily revenue for different services. So rows are days and columns are services with revenue. I have several ...
3
votes
2
answers
102
views
Use variable in row reference in formula
This is my sheet: (shared publicly)
https://docs.google.com/spreadsheets/d/1Pz5dkSHwSXne6f_tMoBJ1Fu3Z6JsvjTQgJZhHhpjia0/edit?usp=sharing
In the "Flatten" tab on that sheet, in A1 I have a ...
0
votes
1
answer
45
views
Filter row based on matching substrings in cell values of another row
I have some data from which I'd like to return only cells that contain specific text.
In Row 1 I have a sequence of numbers, from 1 to 5, and in the row below each number a text string.
I want to ...
1
vote
1
answer
82
views
SUMIF Not working in Google sheets
Found the problem, but not a way to fix it.
I'm European and we use , instead of . to show decimals.
so 2,50 euros for example is a value.
How can i have a formula like =SUMIF(E:E, "A", I:I) ...
0
votes
1
answer
25
views
Can we hide a Vlookup formula to enter new data in the same cell?
I have created a form in Google Sheets:
Row 5, Client, is a dropdown populated by existing clients.
Rows 6 to 9, Street Address, City, State, and Post Code, use VLOOKUP formulas to retrieve data ...
0
votes
1
answer
35
views
Google sheets - Look for a cell linked to a date in a different table and output another cell
I've been strugling with a problem for quite some time.
I'm trying to look up the cell C13 in the top table to output the corresponding number in column A of that Table. So for 01/01 monday it would ...
0
votes
2
answers
31
views
How do I add cells in google sheets to make a total specifically using previous entries?
I'm not sure how to word this, but im creating a pilot episode for a film I'm making and I am not sure what function(s) to do so that when I add a duration to a specicfic shot, it updates a row down ...
0
votes
0
answers
38
views
How to parse multiple cells in one go using ImportJSON()?
I have a dataset with 14 columns. Two of the columns contain text that is in JSON format.
I am currently parsing the data with ImportJSON(), a script that is a collection of custom functions. It has ...
0
votes
0
answers
35
views
Is there a cell formula or macro that I can use to reorganize data on subsequent rows and transpose it to subsequent columns?
I have a large spreadsheet (~1500 rows) that I am trying to manipulate the format of. I have created an example of what my data looks like and shown it here in the first image. I would like it to be ...
0
votes
1
answer
36
views
How do i utilize IF functions (or others) in order to create a simple username/password in google sheets?
On cell A3, there is a username input (i.e max or dave), on cell B3, there is a pin input (i.e 1234 if its max, or 8640 if its dave), how would you go about creating a formula in order to satisfy ...
-1
votes
2
answers
103
views
Formula that calculates the difference between numbers
I'm trying to create a formula that would be able to calculate the difference between the numbers and writes it exactly as shown in this image: https://i.postimg.cc/W4cZDgD2/image.png
Full Example: ...
0
votes
1
answer
24
views
How do I use REGEXTRACT to return grouped numbers from a string where the number of groups varies?
In Google Sheets, I'm trying to return numbers from within a text string.
The numbers are values of the property named either item_id or item_ids.
I've tried the REGEXTRACT function but I'm struggling....
1
vote
1
answer
32
views
SUM cost if Product ID is unique and matches multiple criteria
I have data in a sheet similar to the image. There are rows with duplicate Product ID information and I want to calculate the total cost of a unique Product that matches multiple criteria.
In this ...
1
vote
1
answer
10
views
im trying to get a group of cells and there values repeat along a row. i have values entered from b5:bl5 and i need it to just keep repeating
Google sheets- entered values in B5:BL5 and i need it to repeat across row 5 all the way to og5. I need it to do it automatically. I have tried a scrip that doesn't work cause i don't know what I'm ...
0
votes
1
answer
30
views
All Time Rolling Average based on string order with multiple conditions in the same column - Google Sheets
I'm trying to calculate the average $ amounts per category (X & Y) and their sub-categories (A,B,C,D,E) on a rolling basis based on past data that's on a string format. There are 2 types of ...
1
vote
1
answer
48
views
Rolling Average based on string order with multiple conditions in the same column in Google Sheets
I'm trying to calculate the average $ amounts per category (X & Y) and their sub-categories (A,B,C,D,E) on a rolling basis based on past data that's on a string format. There are 2 types of ...
0
votes
1
answer
59
views
Does Google Sheets have a function to insert X number of Y characters within a cell? [duplicate]
I currently have a spreadsheet which requires a variable number of line breaks within a cell based on input data from another cell.
Long story short, I currently am using a brute force method to ...
1
vote
1
answer
27
views
Stuck at understanding how duplicate prevention formula works in Google Sheets
Many blog posts and educational sites tell us to use =countif($A$2:A2,A2)=1 in the selected range.
However, they don't explain it. And for me it doesn't work and since I can't understand it I can't ...
1
vote
1
answer
36
views
Automatic way to select a range for a formula based on the date
I am creating a calculator where the user describes a list of tasks in Activities!B2:B, and in C2:C they rate 0-5. To separate each day, I've used a date for each task in A2:A (stylistically separated ...
1
vote
0
answers
28
views
ArrayFormula Trouble pulling data for just a year or just last 15 days No understanding how to do [closed]
Google sheet to see my problem
https://docs.google.com/spreadsheets/d/1e3sjPNDJ3crJVQIn9L-lKllXP0wvc9NC22cnjxCveEs/edit?usp=sharing
See separate tab (help)
See all Yellow Boxes........they have the ...