Questions tagged [formulas]

Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.

Filter by
Sorted by
Tagged with
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 ...
user325057's user avatar
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 ...
user avatar
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 ...
cotimms's user avatar
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 ...
Hugh Beyer's user avatar
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 ...
Jann's user avatar
  • 9
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 ...
A.bakker's user avatar
  • 125
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 ...
KyAnSan's user avatar
-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 ...
Anakin Teow's user avatar
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 ...
Luca's user avatar
  • 113
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, &...
google geek's user avatar
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 ...
Kir_Mal's user avatar
  • 41
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 ...
Ramya's user avatar
  • 1
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 ...
Christian Guest's user avatar
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 ...
Elaine's user avatar
  • 33
-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. ...
Michael Langhout's user avatar
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 ...
user avatar
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. ...
user avatar
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,...
Phrogz's user avatar
  • 175
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 ...
Denise's user avatar
  • 11
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 ...
Doragon's user avatar
  • 139
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?
Laura's user avatar
  • 11
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 ...
Swarit Srivastava's user avatar
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 ...
andio's user avatar
  • 377
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 ...
user321796's user avatar
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, ...
Int.Guest's user avatar
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 ...
Steven's user avatar
  • 33
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 ...
ZenLife18's user avatar
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. ...
user avatar
-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'...
ZweiHydra's user avatar
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 ...
dansumption's user avatar
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 ...
Karnak's user avatar
  • 103
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 ...
bw00st3r's user avatar
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 ...
Georgi Slaveev's user avatar
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) ...
A.bakker's user avatar
  • 125
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 ...
Marjorie Reyes's user avatar
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 ...
kyra's user avatar
  • 3
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 ...
Sutter Libby's user avatar
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 ...
Boris Fredoc's user avatar
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 ...
reepjk23's user avatar
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 ...
ZenLife18's user avatar
-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: ...
Ellie's user avatar
  • 1
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....
user319422's user avatar
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 ...
Elaine's user avatar
  • 33
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 ...
Charlie's user avatar
  • 11
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 ...
pfnanc's user avatar
  • 29
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 ...
pfnanc's user avatar
  • 29
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 ...
G0vnah's user avatar
  • 3
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 ...
Saeed Neamati's user avatar
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 ...
Louis S's user avatar
  • 25
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 ...
jim groninger's user avatar

1
2 3 4 5
38