Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [formulas]

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

434 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6 votes
1 answer
15k views

How can I duplicate a row in Google Sheets while retaining all of the formulas and format?

I need help with Google Sheets. We are using a sheet as a working file and new rows will be added by different users. Formatting and formulas are included in various columns. How do I ensure the new ...
ejx's user avatar
  • 61
4 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 ...
Russell England's user avatar
4 votes
3 answers
543 views

Conditional formatting for columns, date & time specific?

I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this: 2018-11-01T20:16:52 I have tried multiple approaches to sorting ...
Cassandra's user avatar
3 votes
1 answer
101 views

Round up duration calculation to the next multiple of five minutes

I currently have this formula: =text(D2 - C2, "hh:mm:ss") I need this formula to round up to the nearest 5 minutes. I have tried mround(), ceiling() and roundup(), but I'm not getting the ...
Cate Kemp  SBFO's user avatar
3 votes
1 answer
345 views

Count number of rows with a specific MONTH in the date of a specific cell and a Boolean condition in another cell

Let's say I have a number of dates and whether they occupy full or half a day: DATE | WHAT | HALF? ... 2021-4-1 | Something Something | false 2021-4-2 | Something Something | false ...
User1291's user avatar
  • 143
3 votes
1 answer
171 views

How do I input a current balance in a cell from a running balance that will be updated frequently?

I know I'm not accurately explaining that but here's a sample spreadsheet of what I mean:
Megan Thomas's user avatar
3 votes
1 answer
6k views

Repeat formula n number of times in Google Sheets

Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
user avatar
3 votes
2 answers
424 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 ...
Elliott B's user avatar
  • 266
3 votes
3 answers
664 views

How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters. I have this formula that only works for any types of duplicate words: =join(" ",unique(transpose(split(A1,", ")...
Max Pacini's user avatar
3 votes
2 answers
94 views

If a cell value is TRUE then retrieve another cell's value

In Google Sheets I have a formula in Column V that returns "Paid" or "To Pay" depending on whether a checkbox in Column U is checked: =IF(U2=True, "Paid", "To Pay&...
Customer Services's user avatar
3 votes
1 answer
255 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 ...
julian's user avatar
  • 31
3 votes
1 answer
210 views

How can I pull a value from one column based on the values of another column?

When the value in the "Amount Owed" column becomes less than 0, I want the value in the "Year" column to appear under "Year Paid Off." For example, if the "Amount Owed" dropped below zero at "Year" 1,...
William Pratt's user avatar
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). ...
Michelle Harris's user avatar
2 votes
1 answer
43 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
  • 21
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 ...
Jack's user avatar
  • 21
2 votes
2 answers
50 views

Simplify formula that checks student year level and mark to a letter grade

I am writing a Google Sheet that checks a student's year level, then assigns a letter grade based on their mark in an assessment. If there is no mark, it is designed to leave the cell blank. The ...
Blake Howieson's user avatar
2 votes
1 answer
58 views

FILTER between columns

I have thousands of data in 2 columns First one is ordered by export and the other by latest activity. The export columns has more queries than the latest activity. (can't change the export to the ...
Quinten De Wilde's user avatar
2 votes
2 answers
84 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 ...
X Luong's user avatar
  • 21
2 votes
1 answer
66 views

Add '/' character in between a number (which should be formatted as a date) and turn it to a date

I have received a spreadsheet which has the dates formatted with only numbers without separators and I would like to transform them into a date format. For example, I would like to change the number ...
Joan MITJANS's user avatar
2 votes
1 answer
37 views

How do I write a formula while referencing another sheet in Google Sheets?

So I want to do a CountA on Column A and Column B in Sheet Two and divide Column A by Column B in Sheet One to get the percentage totals. Can I write all that in one line? =SUM(Sheet!((COUNTA(A2:A516)/...
Lizzie C's user avatar
2 votes
2 answers
193 views

Spreadsheet formula to calculate most common items in ordered items lists

I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items. Like is you have an Oscar ceremony and you get votes from ...
shershen's user avatar
  • 163
2 votes
2 answers
91 views

How to pull data from one sheet to another

Very inexperienced google sheets user here. I have a sheet with employee data. One row and many columns of data for each employee. Some employees appear more than once (i.e. in multiple rows) I need a ...
M. Schultz's user avatar
2 votes
1 answer
326 views

Using REGEXEXTRACT to retrieve specific content from two separate rows in the same column

I Included a Google Sheets link =REGEXEXTRACT(A2:A3, "19..") To me, it would seem as if this would check both cells for any date starting with 19 and extract that into the cell But, it ...
Marshall 's user avatar
2 votes
2 answers
200 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 ...
Jeremy's user avatar
  • 121
2 votes
2 answers
708 views

Conditional Formatting in Google Sheets

The shorter question to spare you from reading more than you need to I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 ...
superluminal's user avatar
2 votes
1 answer
412 views

In google sheets, want to copy cell values from 1 sheet to another if a specific cell has any text

I have this working well: =if('Lee''s sheet'!$AP2="x",'Lee''s sheet'!B2,"") which will populate cell values into a cell, from 1 sheet to another if there is an "X" marked in column AP. I want to ...
Johnny Two Times's user avatar
2 votes
1 answer
187 views

How to generate standard competition ranks (not dense) for my Google Sheet?

What I have is a leaderboard sorted according from high to low scores. I would like to assign standard competition ranks (1224) formulaically. All 7 people who have the highest score (30) should be ...
Rahul V J's user avatar
2 votes
1 answer
889 views

Google Sheets countif attendance sheet, quarterly report

I have a Google Sheet that I use to track attendance at an open lab that I run. Each quarter I put together a report where I count the number of people that attended that quarter. If I just use the ...
amidoingthisright's user avatar
2 votes
1 answer
3k views

Check for regexmatch in multiple ranges in Google Sheets

I have a sheet that auto-populates with a sub-set of articles (each a row with a column representing various information such as title, year of publication, author name, etc...) on the basis of a set ...
Shadow43375's user avatar
2 votes
1 answer
407 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 ...
Yasmeen Elladen's user avatar
2 votes
1 answer
3k views

How can I change the formula of one cell based on the value in a different cell?

COLUMN D: Dropdown list of values COLUMN E: IF function returning different values depending on COLUMN D contents I have the functionality described above working properly. My problem is that I want ...
headroom's user avatar
2 votes
1 answer
38 views

Adding mm:ss times when expressed as TEXT

I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets. The numbers are supposed to be minute second. mm:ss. For the ...
Wish to remain Anonomous's user avatar
2 votes
2 answers
494 views

How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday? This gives me a ...
rosa's user avatar
  • 85
2 votes
3 answers
2k views

How to scan rows for match with ArrayFormula?

Trying to search a row for a given value, but want to make an ARRAYFORMULA so that every time a new row is added the search will be performed on that row. Currently using =IF(COUNTIF($A2:$EI2,"="&...
hartj's user avatar
  • 21
2 votes
1 answer
301 views

How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
Michael Thopmson's user avatar
2 votes
2 answers
835 views

Next Payment Date

I am wondering how to calculate the next payment date a client has. We know when their payment date starts, what the frequency of their payments is, and of course today's date. However, I cannot seem ...
Tempest's user avatar
  • 21
2 votes
1 answer
226 views

Restrict entry to a group of cells based on the value in another cell

I am creating a work schedule where the staff enters their own schedules. I only want a certain number of staff each shift. I already use COUNTIF to show the number of staff signed up for each shift. ...
Frances Davis's user avatar
2 votes
3 answers
3k views

Select last entry from spreedsheet

So I have a Google Form dumping data into spreadsheet tab form responses 1. I have created another tab labeled summary. I need to have the summary tab only show the latest entry for a given pilot ...
Dustin Taylor's user avatar
2 votes
2 answers
48 views

Using multiple IF and OR statements with calculations

I'm trying to calculate unit price based on quantity. I have nested IF functions to check the quantity and multiply that by the correct unit price. Unit Price Table QTY >= QTY <= Unit $   0   ...
Megan C's user avatar
  • 21
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 ...
Ryan William Noon's user avatar
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 ...
que syrah sarah's user avatar
2 votes
2 answers
66 views

Count how many times a certain word comes before and after another in a list

I am trying to evaluate some information from an animal studying exercise using Google Sheets. I'd like to know how many times the animal: forages then allogrooms sleeps then allogrooms etc. Also ...
Lucy's user avatar
  • 31
2 votes
1 answer
490 views

Dividing one column by another based on values on other columns and getting the sum

In the following image, what I'm trying to do is to find a formula that will divide values on column C by values on column L, ONLY IF: Value on column D = "L", and Value on column J = "Right", and ...
Lucas Andrade's user avatar
2 votes
2 answers
289 views

Is it possibile to concatenate with bidimensional arrayformula?

I have a table like this: +----------+----------+----------+----------+ | Photo1-1 | Photo1-2 | Photo1-3 | | +----------+----------+----------+----------+ | Photo2-1 | Photo2-2 | | ...
Kintaro's user avatar
  • 53
2 votes
2 answers
2k views

Using Google Sheets to bulk collect the number of Google Search results for given keywords

I would like to create a Google Sheets that will allow me to paste in a list of 25 or so keywords in column A and would then return the number of search results for each of those terms in column B. ...
user avatar
2 votes
0 answers
68 views

If I ask a yes/no question, am I able to give it a value and add to calculations?

Question on my form: Is your wedding on a holiday? If they answer yes, I'd like to add a $100 value to that answer and then add it to the total. Is this possible? If so, how?
Nancy A's user avatar
  • 21
2 votes
0 answers
698 views

Random #REF! errors

I am a new Google Sheets user, but have been using Excel or equivalent for over 25 years. I have an issue with #REF! errors associated with Google Finance lookup in a sheet I wrote to pull stock ...
Mark1958's user avatar
2 votes
1 answer
3k views

How to sum amount with a criteria separately by day in Google Sheets?

I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month. The file for monthly account contains a couple of sheets - each ...
Pat's user avatar
  • 21
2 votes
2 answers
2k views

Conditionally format cells that not match with range from Sheet2

I have two sheets in the same file where "sheet1" has cleaned up information and "sheet2" has the dump from my Jira. Now I am trying to compare Column F from sheet1 and sheet2 to ...
codingfreak's user avatar
2 votes
1 answer
268 views

How to insert cell text from one cell into an ImportRange Function

Hello! so I have an issue where I am trying to do is have multiple functions in C, D, and E where a link is required. Instead of copying and pasting the link inside all the functions in specific ...
Wences's user avatar
  • 21

1
2 3 4 5
9