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
121 views

Fetch value of a cell based on dropdown menu items results on another sheet

I have 2 Sheets named as Sheet1 and Sheet2... Sheet1: Contains Drop down list on B4 Column. Based on the selection of an item, it pulls the result of the open price shown on D4 column. Sheet2: ...
1 vote
1 answer
9 views

Conditional formating max values over multiple rows

I am trying to Highlight(green) the highest value recorded per month for "orders" and separately for "total amount". I am trying to Highlight(yellow) the highest value of all time ...
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 ...
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 ...
2 votes
1 answer
45 views

How to assign cell values based on options from another list

I am sure there is a simple formula for this but I have not been able to find a solution anywhere. I am trying to fill a cell based on a list in a different tab. In my main sheet, each row has a ...
1 vote
1 answer
25 views

What formula can I use to filter and copy to another sheet

Google Sheets Data Set of 70,000 contact records with multiple duplicate entries where name, address, and email may have changed Data includes: Entry ID, Entry date, First, Last, Email, Street, City, ...
0 votes
1 answer
661 views

How to use FLATTEN function in query function

Below in my query result: In the above table "Form Response" table is the response received in Google Sheets from Google Form. I want to convert "Form Response" table to "...
1 vote
1 answer
613 views

Conditional format custom formula in Google Sheets

Have a question on how to do a custom formula in conditional format for the following: A1 = DCC220 B1 = 7.50 C1 = 7.00 D1 = 7.60 I want to compare the result in B1 (7.50) to a range, C1 lower, D1 ...
1 vote
3 answers
3k views

How to get average from last 3 values?

So I am trying to calculate an average for a golf score. I want the average to be of the persons 3 previous scores. So I am looking for a formula that will average the last 3 numbers in the column. I ...
3 votes
1 answer
254 views

Add values between two dates in Google Sheets

So I've already found this question answered several times around here but for some reason, despite essentially copy and pasting the answers it still doesn't work for me. I'm trying to add ...
1 vote
3 answers
398 views

COUNTIF with function in criterion

I'd like to count all cells in a range where a given function evaluates to TRUE. The documentation for COUNTIF & COUNTIFS show text-matching and evaluation of =,<>,<,>, but nothing ...
3 votes
3 answers
348 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, ...
0 votes
1 answer
788 views

Summing hours spent on a subject in Google Sheets' "Assignment Tracker"

Hi everyone! I love the idea of this Assignment Tracker in Google Sheets. But my goal for time management is to find out how much time I'm spending on each subject. Is there a way to automatically ...
2 votes
2 answers
198 views

Google Sheets SUMPRODUCT and IFS on different sheets

=ARRAYFORMULA(SUMPRODUCT((Products!B2:B=D2)*(IFS(H2="x",Products!D2:D,I2="x",Products!E2:E,J2="x",Products!F2:F)*(E2:E)))) I have a spreadsheet with an Orders sheet and a Products sheet. Product ...
0 votes
1 answer
60 views

Auto sorting / copying data based on inputted data?

Sorry if this is a bit confusing or if I’m not explaining this well enough. Using my provided picture as an example how would one auto copy data that gets inputted into that table based on estimated ...
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 ...
0 votes
2 answers
893 views

Find a cell containing text in a range and return the text in that cell

I have a range of data where each row is in alphabetical order and I want to pull each cell in the row that contains "client" to another column.
2 votes
2 answers
83 views

Check if a string appears within another cell, and return the longer of the 2

I am having a hard time thinking of an formula that can help me pull the data needed in this example: In the C1 cell, I would like to make a formula that does this: Check if any cells in the A ...
1 vote
2 answers
1k views

Google Sheets VLOOKUP with wildcards on range

SETUP Say for a simplified example I have a table of raw data from my bank displaying each store I've spent money, and I want to categorize these into either high-street, website, or pub venues. The ...
2 votes
1 answer
118 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 ...
1 vote
3 answers
50 views

How do I inhibit conversion of `(3)` to `-3` within a Google Sheets formula?

=index(split("A (2)"," "),2) renders as -2 even when cell number format is set to "Plain text". It also breaks further functions like REGEXREPLACE that expects text, not ...
0 votes
1 answer
2k views

How to use a UNIQUE formula with an IFS formula in Google Sheets

For example if a Uniqueifs existed, I'd have =uniqueifs('Data'!H2:H10000, 'Data'!F2:F10000, A18) If a cell in the range of 'Data'!F2:F10000' contains the contents of the cell A18, then.. Print out ...
1 vote
1 answer
79 views

SUM across other worksheets (without hardcoding their names)

I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets ...
0 votes
1 answer
22 views

Connect Participant Scores(Column D) to their id Numbers(Column A) by sorting Columns B and C

I have 4 columns of data. I am trying to connect the Scores to the idNumbers. Two sets of data. Column A and B are idNumber and Member names. And Column C and D are Participants and their Scores. The ...
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 ...
0 votes
1 answer
414 views

Return a list if 2 criteria match in Google Sheets

In an effort to organize staff training: So far I've made the pivot table, but I'd like for the pivot table to extract who needs which training, as a list in one cell. I tried using =JOIN, but I have ...
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 ...
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 ...
1 vote
1 answer
354 views

A Master Sheet / Template for Google Sheets similar to a Slide Master for Slides/Powerpoint

How do I create a Master Sheet with formulas that, whenever those formulas are updated, it updates those formulas in sheets made by copying it? So whenever changes are made to the Master Sheet, linked ...
0 votes
3 answers
79 views

Using a FILTER function in conjunction with other functions

sample sheet I am looking for a function to display the Title (from column B) that has the lowest numerical average in column (found from column G). I have that already in the form of =INDEX(B2:G,...
1 vote
1 answer
75 views

How to combine ARRAYFORMULA + QUERY?

"Please advise on how to correctly input the formulas to get the desired result as shown in the picture. Task: https://docs.google.com/spreadsheets/d/14tpPliM_3b7RIikSimvdzLjkFq3CexPKbtOEuD4aIKU/...
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 ...
0 votes
2 answers
78 views

Create sequential dates with each date repeated multiple times

For every day of the year, I need the day repeated in 10 rows. How can I accomplish this efficiently? e.g. 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/...
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 ...
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 ...
1 vote
1 answer
976 views

Stack two sets of data into one in google sheets - different columns

I am looking for a solution that helps me stack two sets of data (from diff. columns, different order) into one data sets EXAMPLE: columns names + order tables1: first name, last name, city, age, ...
0 votes
1 answer
35 views

Using created filter to filter specific value base on other cells value

I can't seem to figure this out. I am wonder if it is possible to create a filter for a column, and then filter that column based on another cells value. So I have created a filter for A column which ...
0 votes
1 answer
133 views

Is there any way to set different formulas for multiple ranges all at once?

In my question Is there any way to set different values for multiple ranges all at once?, I found out how to set different values across dis-contiguous cells all at once, and we developed a good ...
0 votes
1 answer
34 views

Comparing data changes over time?

Let’s say I’ve collected data on a scale of bins from 1-6 at two different time periods (T1 & T2), organized as two columns in Google Sheets: T1 T2 1 2 1 5 2 3 5 ...
2 votes
1 answer
2k views

Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?

I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
0 votes
2 answers
136 views

Data validation rule that accepts only 16-digit numbers

In Google Sheets I am trying to create a custom data validation formula for a column. I only want 16 character values made up entirely of numbers (e.g. 0-9). I tried using a formula that works for me ...
0 votes
3 answers
3k views

Increment the reference to a cell formula by 1 in Google Sheets

So, I'm wanting to use the following line of code: =image(CONCATENATE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&A2&+B2&+C2&+D2&+E2&+F2&+G2)) ...
0 votes
1 answer
967 views

Search across multiple columns for a value(s) return multiple row results

I am a rookie at this and I spent hours trying to figure this out but can find anything that really helps. Scenario 1: Looking at the photo. I want to search columns C through M for a selected color(s)...
1 vote
1 answer
24 views

Conditional Formatting Help

I must be having an off day as I'm usually good with formulas - but I'm trying to finalize something for work and my brain is not getting me there.... I need a formula to color cell G75 Orange if cell ...
0 votes
1 answer
26 views

Formula not working where the row reference is stored in another cell

I have a formula where the sheet name and column always stay the same, but the row changes based on the row number I have stored in a cell: ='Sector info'!(CONCAT("C", B2)) In the formula ...
1 vote
1 answer
426 views

SUMIF or IF when sum is more than zero

In Google Sheets, I would like to get - at the bottom-most cell of a column - the sum of each cell (except for row 1, the header) above in that column ONLY if the total is greater than zero, (I don't ...
1 vote
3 answers
2k views

Google Sheets COUNTIFS Range of cells as criteria possible?

In the Google Sheets Formula =Countifs, is it possible to have a range as a criterion, for example: =COUNTIFS(ExampleSheet!A1:A10, "Some criteria", ExampleSheet!B1:B10, List_of_Names!C1:C) I couldn'...
1 vote
1 answer
122 views

Google Sheets: Search for matching words in a list based on the last word in a cell

UPDATE Now it only need to scan the whole row instead of only the cell next to it. I am quite new to google sheets and I want to achieve the following: Lets say we have list 1: Red apple yellow ...
0 votes
1 answer
2k views

ImportRange not pulling in Dates from first sheet in correct format

I'm trying to pull in a date cell from one sheet to another with an importrange function: =iferror(query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UClhpev01z-...
0 votes
1 answer
102 views

Google sheets: auto sorting based on color and alphabetic order

So I have this list: B (white background) A (white background) C (green background) And I would like to make a function where I sort it as follows: C (green background) A (white background) B (...

1
2 3 4 5
42