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

Google Sheets - Filtering Chart Data with Checkboxes

In Google Sheets, I'm making a 'workbook' that keeps track of inventory in a shop. There are 3 sheets in the workbook. The first one is the raw data which is 'Item Name', 'Initial Stock', 'Stock Used',...
0 votes
2 answers
51 views

How to work over rows from all other sheets in Google Sheets?

I have a 'summary' sheet and 12 other sheets named 'Jan20', 'Feb20', etc. In the summary sheet I have this formula (to calculate Uber costs): =FILTER('Jan20'!F2:F, REGEXMATCH('Jan20'!D2:D, "UBER")) ...
1 vote
1 answer
2k views

Google Sheets - Split row into multiple rows

Current: ------------------------------------------------------------------- | Amy | Adam | Bob| Cliff | Dave | David | Derek |...| Zachary ------------------------------------------------------------...
1 vote
1 answer
1k views

Arrayformula for running MAX of separate column

I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
-1 votes
2 answers
51 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: ...
1 vote
1 answer
23 views

Countifs in between ranges

I'm struggling to pull below conditions together. I'd like to have a formula that will count values between 1 and 5 only when cell U13 contains the text 1-5. =COUNTIFS(U13,"1-5") =COUNTIFS(K2:K22,"&...
0 votes
1 answer
24 views

Filtering by flight times in Google Sheets

I have flight delays ranging from hours ahead of schedule to delays of all durations. I need to countif any delay that is longer than 20 minutes. The values in the column take on this format: "-0:...
1 vote
2 answers
154 views

Function to search columns and tally data

I am wondering if I can write a function to search a column in my spreadsheet for a specific name, then return x number of values within the row(s) that contain the name. For example... M4 holds the ...
1 vote
3 answers
32 views

How would I SUM column values while excluding any that precede the text string "reset"?

I have a spreadsheet where the hours of wear for pieces of equipment are logged: Data is logged in new rows as additional wear-hours are put on the equipment. From time to time, equipment is replaced,...
2 votes
1 answer
371 views

When I insert a row, indirect does not update

I have a summary sheet and I would like that every time I add a new sheet everything gets referenced to the summary sheet. I have used indirect to do this, the only flaw is that once I add a row, the ...
0 votes
1 answer
2k views

Average value using different sheets on Google Sheets

I have a data set that's separated by year (so, the first year is Sheet1, the second year is Sheet2, and so on. There are 18 years in total). I want to average the data from different years. At first, ...
2 votes
1 answer
1k views

Conditional Formatting across a range of cells with a formula in Google Sheets

I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018". I have a separate sheet with a data set that has rows that classes end such as "9/24/2018". I want to ...
0 votes
1 answer
82 views

How to filter an spreadsheet using filters + drop down feature in google sheets

I've created an spreadsheet and I'd like to filter the information in the rows using a drop down filter. I've managed to create the drop down menu in A3 but now I'd like to show the information in the ...
2 votes
2 answers
170 views

How to make sequential list of months?

I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
0 votes
1 answer
48 views

Google Sheets formula

I'm using Google Sheets make a spreadsheet with the name in column a, the company in column b and in column f, I need their email. I have 3 companies, and they have slightly different email formats. ...
1 vote
3 answers
2k views

How to create a formula in Google Sheets that can sum specific cells based on multiple criteria

I am wanting to create a formula that will look at a dozen different cells and sum to the total value of those cells but some cells will not be numbers and I want to exclude those cells in the sum. ...
0 votes
1 answer
127 views

Query Problem: Function QUERY parameter 2: NO_COLUMN: Col64

I have been using this formula in a document for quite some time and have had no problems with it. Now however when I opened it up, it suddenly stopped working. When I revert the document to an old ...
3 votes
1 answer
3k views

Format telephone numbers, keeping only digits

I have a spreadsheet that has phone numbers in the cells that are typed out like this: +1(313)313-3333 and I need these numbers to all be plain and look like this 3133133333 (with no 1 in front). ...
0 votes
1 answer
165 views

Find whitespace and replace with string from formula in Google Sheet

I have list of cities and I want to create URL for each of them in Google Sheet. Scenario is most of the part of the URL is same only at the end city name is different so I created a formula =...
1 vote
1 answer
22 views

Jump to cell address mentioned in other cell

A1 shows address of last cell un a column (e.g. A572 or A577). Every time address will change if adding new row (there can be blank rows in between of rows with data). I need a link in B1 to click and ...
3 votes
1 answer
1k views

What ticker symbol to use for the crude oil price?

Which ticker symbol can I use for the crude oil price for the GOOGLEFINANCE function? Nasdaq Brent oil is "BZ:NMX" https://www.nasdaq.com/market-activity/commodities/bz:nmx Nasdaq Crude oil ...
2 votes
5 answers
14k views

Identifying a merged cell in Google Sheets without using a custom function

I am using Google Sheets and I am trying to find a way to identify merged cells without using a custom function. For example, I need to determine (using only the built-in functions) whether any ...
3 votes
2 answers
2k views

How can social hashtags be extracted from a cell?

Example: A2 -> What a beautiful day! #sunday #summer B3 -> #news Stay tuned for our next contest! #something Desired output on B2 ->#sunday #summer Desired output on B3 ->#news #...
1 vote
1 answer
47 views

How to categorize data according to the first n characters of its number?

Sorry for the confusing title, my problem is this: I have a table of items, like this: A B C 1 Number Name Category 2 100-150-135 Mike - 3 150-150-133 Joe - In the same document, I have another ...
1 vote
2 answers
65 views

Show each n-th column, hide others in Google Sheets

I have several big tables, and I know that each 4th column is interesting for me, i.e B,F,J,N, etc (The total number of columns is different in different documents) Is there an easy way (some sort of ...
0 votes
1 answer
19 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
35 views

Need To Randomize Selections From A List But Ensure Uniqueness

I'm building a tool to randomize setup for a very large modular card game. There are several lists from which I need to select 2 to 6 non-duplicate entries, and each list is on a separate tab from the ...
0 votes
2 answers
806 views

Conditionally formatting duplicate values in Google sheets with exclusion criteria?

I'm currently working in a Google sheet where I use the following formula to catch duplicate values with conditional formatting: =countif($B:$B,B2)>1 While this works on its own, I find that it ...
0 votes
2 answers
30 views

How to get data from table to another when you can't re-order columns

Having trouble putting this into words, reading countless threads describing issues similar to mine, but not this exact one. So here's a super simplified version of what I need done. I have numbers ...
1 vote
1 answer
8 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
28 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 ...
0 votes
0 answers
22 views

Getting just month and year from a date [closed]

I have a data sheet in Google Sheets that has a date time column that I need to isolate just the month and year from in a mm/yyyy format. The original column looks like this: 27/09/2021 10:13 I used ...
0 votes
2 answers
298 views

How do I advance the column instead of the row when I copy/paste a formula?

The following formula references a cell in a separate sheet: =('Form Responses 1'!B3) If I copy that formula to the row below, the formula changes to: =('Form Responses 1'!B4) The cells I want to ...
0 votes
1 answer
374 views

Extract Data from 2 columns if it matches condition

This is how my main data sheet looks like And for any item that has customization, for e.g. - ORGANIC, I want to extract/copy just those data, the meal name & total units to another sheet. And ...
1 vote
1 answer
150 views

Can I populate Google Sheets cells adjacent to a target cell?

I have a maintenance tracker in Google Sheets for lighting fixtures. One column is a type of fixture "B", the column adjacent is the problem "C". I'd like to find specific fixtures by name and copy ...
0 votes
1 answer
133 views

Conditional formatting with multiple names in list

Col A2:C15 contain a list of names. I need to enter those names in various combinations into one of 15 other cells on formatted like: H3 = John, Steve, Bob, Dan Is there a way to use conditional ...
1 vote
1 answer
411 views

Google Sheets Query: Order By and accented chars

In a Google Sheet, if we have a column with accented character strings, the sort order of menu option "Data -> Order sheet by column" correctly ignores the accents (ordered column A in ...
1 vote
2 answers
120 views

How to return a value from a row from the last column a text appears

I am having problems returning the correct value i need. I enter data on this table. And i want to return some values on the other table. In that second table i want to return the last date his name ...
0 votes
1 answer
293 views

Google sheet - highlihghting cell if in the same row other cell not empty

I'm trying to make a worksheet where I log shift hours. I made columns with dates and to each date a row of hours I want the date text to be green if in the same row a cell is not empty.. The code I ...
0 votes
1 answer
1k views

Generate "cards" with info in Google Sheets

I have a sheet for an event and I need to generate identification cards like in the image for the pieces on the contest, around 1000 pieces, so no way to manually do it. I did a similar thing years ...
-1 votes
1 answer
28 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 ...
4 votes
3 answers
19k views

Making all cells in a column links from the values within the cell? [closed]

I have a column of cells in a Google Spreadsheet with values such as: 512 2123 2342 3532 What I would like to do is convert all these into a hyperlink and keep the value as the link text: http://...
0 votes
2 answers
165 views

Is it necessary to use ArrayFormula to get the SUMIF function to work properly in Google Sheets?

I am trying to find the sum of values in a column based on the month in an adjacent column. For example, Sheet 1 has Three columns, one for student remark, one for payment date, and one for amount. ...
0 votes
1 answer
19 views

Can the formula of a cell be conditional based on the dropdown selection of another cell?

Is there a way to create a formula where D9 is included/excluded from the formula based on the answer to the dropdown selection of D13 (Financing Repairs? Yes/No)? Spreadsheet I've spent hours ...
0 votes
1 answer
464 views

Google sheets join arrays

What I'm trying to do is to join few arrays of dates. For start, I have start date and amount of months I want to generate. Which I did by using =ARRAYFORMULA(DATE(YEAR(A2), MONTH(A2) + SEQUENCE(C2, ...
0 votes
2 answers
38 views

"IF" Functionality Inquiry

So I am trying to figure out how to whenever it's a "Y" and "INCOMING", then it will equal a dollar amount. So as if a + b = 1 then equals $35 =COUNTIFS(F3:F100,"Y",B3:B100, "INCOMING") I did it a ...
1 vote
3 answers
34 views

'Large' 'If' Formula Help

I am looking for a formula I can put into Google Sheets that finds the 1st, 2nd, and 3rd largest values in column B, but only for rows that also contain the number 1 in column A. Problem is, column A ...
0 votes
1 answer
150 views

How to make formula based off a withholding table?

I have been attempting to make a formula to easily calculate federal income tax rates. The picture attached is the withholding table that I've transferred onto a sheet. These are the thresholds I am ...
0 votes
1 answer
25 views

Does it help to close a Google Sheet when it's slowed down due to "Calculating formulas"?

It's known that "Calculating formulas" in Google Sheet disaster is a problem in general, but I have a specific question - does it help at all if I or other concurrent users exit the sheet or ...
0 votes
1 answer
477 views

Conditional Formatting in Google Sheets doesn't work in certain cells with custom formula

Custom Formula doesn't work on certain cells. A little background, I am making a timezone sheet for a discord where someone input the time they wanted and checks who is and is not available at that ...

1
2 3 4 5
41