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
1k 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
16 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
81 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
47 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. ...
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). ...
1 vote
1 answer
71 views

Assigning a value to a name from a list

I have items that I'm selling, and I've named these codes with the item I'm selling. I want to give these codes the price they should display. I've already got a formula to show the product of the ...
0 votes
1 answer
27 views

How to make an alternating value in a single cell?

I want to make a cell swap between two values every couple of seconds, but I don't know how. EX: A1=1, then a second later A1=0, then a second later A1=1, and it repeats like that, does anyone know ...
0 votes
1 answer
96 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
19 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 ...
-4 votes
1 answer
2k views

Simple row references in Google Spreadsheets and resorting data

This is my scenario Users are entering data into rows with several columns Last column is calculated on values of few previous columns (so it's row number independent). While entering new and ...
0 votes
0 answers
28 views

VLOOKUP or INDEX/MATCH used on a HTML Import [closed]

A little background, some friends and I wanted to start a Fantasy Golf league. To start we would pick our teams and fill in manually. I did some research and started to try and automate the process. ...
1 vote
2 answers
77 views

Google sheets range of sheets? [closed]

I have multiple sheets in my Google Spreadsheet. Let's suppose that they are named "1", "2", ... "n". I would like to sum the contents of the B4 cells in all of these ...
1 vote
1 answer
32 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
54 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 ...
-1 votes
1 answer
78 views

I am trying to find a custom function to find the min value of coloured cells ONLY for a range where some cells have a specific color [closed]

I am trying to find a custom function to find the min value of coloured cells ONLY for a range where some cells have a specific color. The best I could find is to count the cells with a certain color. ...
0 votes
1 answer
15 views

SUMIF sometimes not summing when criteria is met

SUMIF is not summing some of the cells despite the logic being met. For example, cell B9 on sheet 3 should result in 25, but it is coming back with 0. I've tried sumif(Sheet2!$B26:$O26, "*MW*&...
1 vote
1 answer
33 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
746 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
23 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 ...
0 votes
1 answer
184 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
118 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
110 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
2 answers
30 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 ...
1 vote
2 answers
3k views

Sort by IP in Sharepoint list

I have a column in Sharepoint containing IP-addresses. I can't figure out how to sort them correctly. They are being sorted like this: 192.168.1.1 192.168.1.10 192.168.1.100 192.168.1.2 Rather than ...
0 votes
1 answer
202 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 ...
0 votes
1 answer
331 views

How do I calculate weighted rates in Google Sheets? [closed]

I would like to calculate the cost on $D "Rent" with a weighted cost when the respective cell in $B="PB". The calculation would be based on the formula 7x+1.3x=1252 where 7 is dependent on COUNTIF(A2:...
0 votes
1 answer
14 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
2 answers
37 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 ...
0 votes
1 answer
443 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, ...
3 votes
3 answers
3k views

How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?

I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I ...
0 votes
1 answer
60 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 ...
-1 votes
1 answer
19 views

Two drivers, two calculations, one cell [closed]

I have a trucking business with two drivers. I receive a value ($) for each load my business delivers. I pay a share (%) of that value to the driver of the load. The share for "Driver1" is ...
0 votes
1 answer
214 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 vote
1 answer
467 views

How to get weekly averages for workout tracker data in Google Sheets?

I am making a personal training app that uses data from Google Sheets. I want to create a formula that takes the data from a sheet of workout tracking inputs and creates a weekly average for each ...
0 votes
1 answer
76 views

Need a cell to be BLANK - NO ZEROES - if no data is input into the FORMULA RANGE above?

I'm trying to make it so that a column of data sum when data is input into the column above, but remains BLANK if there's no data. I've been able to accomplish this with AVERAGING, using this formula ...
0 votes
1 answer
129 views

Google sheets reference Text in Cell in Array Formula & Countif formula

So, I have a Array Formula and Countif formula in place because I need to know the total of entries in a combination of ranges. I need to know the sum of the number of cells that have "X" ...
1 vote
1 answer
2k 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 ...
1 vote
1 answer
174 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 ...
1 vote
1 answer
41 views

Is there a formula to count occurrences of a string in another sheet when the string needs to be "looked up" in a different sheet? (Quiz example)

Sorry for poor title wording. I am keeping track of quiz questions in a Google Spreadsheet. In order to generate question statistics I also keep track of some metadata about each theme in a different ...
0 votes
1 answer
33 views

Import and SUM chosen (from drop-down) column to Sheet1 from Sheet2

What formula I can use in cell C2 to import one column from sheet 2 using a drop-down list? Here's the document.
0 votes
1 answer
2k views

Time format when adding hours in Quip

After some deep research, I've been looking for a way to sum/subtract hours in Quip Spreadsheet (Salesforce subsidiary). Apparently, time formats such as 25:00:00 are not listed in their menu Format &...
1 vote
1 answer
3k views

How to automatically set date in a cell when a different cell value is changed by a formula from data on another sheet?

I currently have a column, e.g. ColB, containing SUMIF functions with its data range from other sheets and a column, e.g. ColC, with dates that I want to automatically update to the last date ColB's ...
1 vote
1 answer
32 views

How to add text conditional on numerical ranges in Google Sheets?

I'm using Google Sheets and I want to display certain text based on the numerical value of another cell. I am a teacher trying to create an assignment where students have to find the best zombie ...
0 votes
1 answer
27 views

How to extract desired text from a cell

As you can see below I have these data in single cell I want only car nos as my result
1 vote
1 answer
2k views

Auto hide row based on cell text (formula based)

My plan is to hide an entire row if a specific cell in the row showed specific value. I already found the script and the script work amazing. But the problem now, since the script use onEdit function, ...
2 votes
1 answer
50 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 ...
0 votes
1 answer
13 views

Google sheets. Compare the value in column on the 1st sheet with 2 others and then pull cells to y the right of matching value in the other sheets

I have one workbook, 3 sheets. Ultimately I'd like information from sheet 2 and 3 joined with corresponding information on the 1st sheet. The 1st column has an ID number on each page. How can I make ...

1
2 3 4 5
40