Questions tagged [formulas]

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

Filter by
Sorted by
Tagged with
1 vote
1 answer
25 views

Conditional formating max values over multiple rows

I have Google Sheets data in column pairs from Column B to AQ. Each column pair represents a single year with the first column containing order counts, Orders, and the second containing order totals ...
Wes B's user avatar
  • 11
4 votes
2 answers
2k views

Converting "4.8 million" into an actual number

I have a sheet that has numeric records that are a mix of three different number types: 260,000 4.8 million 870 million 1.1 billion The first one is handled just fine as a number, but the ones ...
RobKohr's user avatar
  • 143
3 votes
3 answers
350 views

Formula to count rows between positive values

I need a formula in Google Sheets to count the number of rows between each positive number in a column. In Column B of the table below, for every value in Column A that is less than, or equal to zero, ...
Yu Lun CHEN's user avatar
0 votes
2 answers
21 views

I wanted to set the maximum value of each unique input as active

In the example data below, for each unique SYS Name, the Status column is marked Active in the row with that SYS Name's highest Serial Number. In Status, Column C, for each unique Sys Name the row ...
Vintikon Indonesia Perkasa's user avatar
2 votes
1 answer
122 views

alphanumerics in duration in sheets - how to convert to a number of minutes

Working in google sheets, I am getting input from another program in the form of "Xh Ym Zs" to track time. I'm trying to write a formula that will just yield a number of minutes, so "2h ...
user8531's user avatar
1 vote
2 answers
32 views

How do I make dropdown selections add value to a totals column in Google Sheets

Very new to Google sheets and very lost. I am trying to create an awards tracker for school Merits 2024 Ideally, I want to include drop down menus with options for each week we hand out merits at ...
MHLO17's user avatar
  • 13
1 vote
2 answers
40 views

Formula to calculate the sum of consecutive 0's and -1's

I am hoping someone can help me with a Google Sheets formula to calculate the minimum sum of consecutive 0s and -1s in a column. 0s and -1s are consecutive until interrupted by any other integer. For ...
Will's user avatar
  • 13
1 vote
1 answer
21 views

Formula to return a value when the adjacent column's text contains any value from an array of text strings

In Google Sheets I have an array named "Tech_issues" with the following values: Array name value 1 value 2 value 3 value 4 Tech_issues crash froze glitch error I'm looking to create a ...
flipskyrain's user avatar
0 votes
0 answers
17 views

Google Sheet formula to search all columns that match a specific keyword and sum values from them [duplicate]

I'm trying to paste a formula on cell AS2 that searches the column names on Row 1 for an occurrence of a keyword and if that column has such a keyword on its title searches Row 2 for numeric values ...
MACC's user avatar
  • 101
2 votes
1 answer
60 views

Can an Google Sheet Filter function base its include on a whole row?

In Google Sheets I have two or more columns of data: Column 1 is a list of items that may include duplicates. Column 2 (and any additional columns), contains keys each associated with the item from ...
LWC's user avatar
  • 1,228
-1 votes
1 answer
24 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
57 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 ...
dee123's user avatar
  • 23
0 votes
1 answer
27 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
38 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
30 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
37 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
48 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
43 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
41 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
23 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
41 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
40 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
95 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
84 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
  • 397
2 votes
1 answer
45 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
186 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
39 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
153 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
118 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
93 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
27 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
39 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
37 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
2 3 4 5
38