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
1answer
6 views

Multiple Columns, Multiple Criteria Count

Sorry if I don't word this perfectly, but I'm trying to count multiple options in multiple columns. The current formula I've attempted is: =COUNTIFS('Data Dump'!AA:AA,{"Awaiting Copy","...
0
votes
2answers
44 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")) ...
0
votes
2answers
3k views

Google Sheets - Formula to find difference between two values and recognize if the number difference is positive or negative

Hope my blabbering will make sense to someone. I'm trying to create a spreadsheet in Google Sheets to record profits and losses. I've used the =ABS(A1-A2) formula to get the differences in between two ...
0
votes
1answer
18 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
2answers
108 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
1answer
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 ...
1
vote
1answer
150 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
1answer
939 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
2answers
73 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 ...
1
vote
3answers
1k 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
1answer
13 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 ...
2
votes
1answer
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
1answer
28 views

Need help with Google Sheets!

0 I need help figuring out a formula. In this example: Column E is Start and F is Finish. I want to write a formula in column J that writes an "X" with the following conditions: E > 7:00 ...
0
votes
1answer
44 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. ...
0
votes
1answer
16 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
1answer
567 views

How to convert multi-value answers into single column in Google Forms' response sheet?

I have a Google form that asks for Name and up to 3 Score. The data is dumped into a sheet like this But that data is not very convenient. I want to have the data like this... Can I do that by ...
0
votes
1answer
34 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
1answer
11 views

Google Sheets IFS Text result based on multiple conditions FIX & SORT

I'm very new to Google Sheets and I'm trying to make find the correct formula for a text result based on multiple conditions, here is my best crack at it: =arrayformula(IFS(B9:B="",V9:V=&...
0
votes
1answer
21 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 ...
0
votes
1answer
25 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
1answer
36 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 ...
0
votes
1answer
89 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 ...
1
vote
1answer
19 views

Using multiple IF statements to return a value based on multiple ranges

I'm trying to write an IF statement for whether an engine size is small (900 - 1400), medium (1401 - 2000) or large (>2000). The engine sizes are in the B column. I'm not really sure how to ...
0
votes
2answers
645 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
1answer
73 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 ...
0
votes
2answers
527 views

Formula for custom number format in Google Sheets (e.g. 5M --> 5,000,000; 50k --> 50,000; etc)

I've been trying to figure this out for a bit but everything I've tried doesn't work. A quick search brought me to the conversion from full number format to condensed format (5,000,000 --> 5M; 50,000 -...
0
votes
1answer
998 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
1answer
43 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
1answer
251 views

How do I calculate weighted rates in Google Sheets?

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
2answers
34 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
1answer
342 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, ...
1
vote
2answers
2k views

Conditional Formatting based on other column

I have a timesheet log with columns DATE | HOURS | DESCRIPTION OF WORK | PROJECT An employee may enter several rows for the same date to log the different amount of time with respect to the ...
1
vote
1answer
46 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
1answer
33 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
1answer
894 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 ...
0
votes
1answer
133 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 ...
4
votes
3answers
8k views

Query or filter the data where the start time is past 3:00 pm

I've started using Google Sheets for our logbook and it's been a great help. I have data with a time component in it (start and end time) and I'd like to query or filter the data where the start time ...
0
votes
1answer
29 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.
1
vote
1answer
187 views

Finding the longest time duration in Google Sheets

I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time. =LARGE(...
0
votes
1answer
325 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 &...
0
votes
1answer
37 views

How to insert first numeric value in a column?

I'm trying to create a formula which will in the absence of data for itself, reference a column for the first piece of numeric data. However, I'm not sure what formula I should be using. Below is a ...
1
vote
1answer
2k 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
1answer
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, ...
1
vote
1answer
21 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
1answer
18 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
0
votes
0answers
28 views

How to create a google sheets formula to return a value based on % values in other cells & weighted average formula? (FIXED)

I've been trying to create a scoring system for expected stock market returns - Sheet I'm having problems creating a formula to firstly give stocks a score in multiple conditions for example SS I want ...
0
votes
1answer
16 views

How to make an output of a column that "filters" through another?

Essentially, I have a Column A with a bunch of websites, and Column B with a bunch of acceptable websites. I'd like column C to display the websites from Column A, except those that contain url's from ...
0
votes
1answer
2k views

Concatenating URL with a value to create link, but links only partially work

I have a column of cells in Google spreadsheet with values such as: b2397 b9504 b3333 b8655 bxxxx I would like to combine each value with a URL, for example: http://websitex.com?id= ...
0
votes
1answer
44 views

How to check separate sheet cell for any data, and return a Y?

I'm using several Google sheets to capture product data and optimize it. One sheet will feed data into the next, and then again into another sheet, using this formula: =IMPORTRANGE("https://url-to-...

1
2 3 4 5
35