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

Syntax of field “Google Sheets / Pivot table editor / Filters / Filter by condition / Custom formula is”

In Google Sheets, if I create a pivot table, there is a Pivot table editor I can use to configure the pivot table. The lowest section of the interface of the editor is Filters (which allows me to add ...
2
votes
1answer
20 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 ...
3
votes
1answer
32 views

Filter and match multiple columns in Google Sheets

I have a list of teams and their corresponding owners in Google Sheets. (owners own multiple teams) I have a great formula that will filter the list by owner name: =FILTER(A2:A6, MATCH(B2:B6,"RINGO",...
0
votes
1answer
20 views

Trying to make a column of “select count” in reference to another column

I am pulling data from multiple sheets in my workbook to create an array. In the first part of the array I pull data straight from a sheet like a user name # of points and rank. Now for the rest of ...
1
vote
1answer
21 views

Import cells into the text of another cell preserving quotation marks

Well, I did some research but I didn't find what I'm trying to do. I have this text: "en":"B2", "es":"C2", "it":"D2", "pt":"E2" I have 4 columns with information in each cell. I need to "import" ...
1
vote
1answer
36 views

QUERY function to search three columns for the same keyword?

I have a table with data in columns A-U. Rows go down to 368. I would like to pull (onto a different sheet) all the information in columns A-F where it matches a keyword from a dropdown menu (in cell ...
0
votes
1answer
12 views

When editing a formula in Google Sheets, how do I resume 'range selection mode' so that I can select a range with the mouse or keyboard?

When you first enter a formula in Sheets you can use the mouse or cursor to select the range. If I want to sum the 5 cells below the cell with the formula, I just type =SUM( and then use the down ...
1
vote
1answer
17 views

Add Two Weeks (Weekdays) on Data Validation

I am needing to add two weeks on to date in a separate column. In one column (A) I have the "Date Requested" and in the next column (B) I have "Due Date" I need the due date column to have the "Date ...
1
vote
1answer
15 views

How do I make the “Text contains” evaluate any word written in the cell?

I just want the cell in Google Sheets to change its fill color when any text is inputted in the cell? But it has to be text excluding numbers. Please help!
1
vote
1answer
22 views

Finding merged cells in Google Sheets

I'm often trying to freeze columns in a Google Sheets and I get the error as below The way I get around this is to manually hunt for the merged cells and unfreeze them, but this can be difficult on ...
1
vote
2answers
15 views

Use Google Sheets to create column C by deleting any phrases that contain word found in column B in column A

I have a list in column A and I want to remove any cells that contain any word.
0
votes
2answers
42 views

Extracting last match from a string with REGEXEXTRACT

I've created a simple text filter: =IFERROR(REGEXEXTRACT(A1;"- May 2019| - Jun 2019|- Jul 2019| - Aug 2019|- Sep 2019"); FALSE) It should extract the month and year for an event from the cell A1. A1 ...
0
votes
1answer
17 views

How to find the last value in a column when the column is populated from an ARRAYFORMULA

Some columns in my sheet are filled manually, but then two columns are created dynamically from an ARRAYFORMULA (i.e. there is nothing in the column if the manually populated cells are empty, but once ...
1
vote
2answers
40 views

Inserting error (or spacer) when IMPORTXML can't scrape data in Google Sheets

I am using IMPORTXML to create what is essentially a vehicle inventory feed file. My issue arises when a price is not included in our main website. What seemingly is happening is when the price is ...
1
vote
3answers
40 views

How to use QUERY to show data in Google Sheet using tags

I have a Google Sheet with 2 sheets. In sheet 01 I have this table. In sheet 02 I want to have a "Search by Tag" sheet. So, googling I got this formula =QUERY('01'!A2:D4, "select B,C,D where A = '...
1
vote
2answers
52 views

I am having trouble using indirect to access a cell in a different Google Spreadsheet

I have 2 spreadsheets, Test1 and Test2. In Test2, Cell B2 I want to access a cell in test1. I want to use the value in cell A1 (the value is test1) indirectly to access cell A2 in test1. This is the ...
0
votes
1answer
15 views

Merging cells based on same ID but divided into categories (columns)

I receive a pretty extent list of material that has been delivered and every single product is in a separate row. That means for one order I have easily a lot over 15 rows and I need to merge the ...
0
votes
2answers
23 views

Google Sheets duration format does wrap around 24

If I format the cells of a sheet to duration, it wraps around 24h So adding those three cells (which should represent HH:MM): 05:00 18:00 02:00 with SUM, the result is 01:00 where I need the result ...
0
votes
2answers
30 views

How can I make sure an equation doesn't give an answer if more than one cell is blank?

I have half the equation working: =IF(AND(IF(NOT(ISBLANK(AG22)),TRUE,FALSE),AJ22<AG22),"1","0") So it looks like this: What I WANT it to do, is to only prompt an increase or decrease response if ...
0
votes
2answers
18 views

How do I “bin” results in Google Sheets?

I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale. For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or ...
0
votes
2answers
21 views

Show values from only rows that have a checkbox ticked

I have a data table with groups as column headers and names and emails as rows. I want to export data to another spreadsheet in the same document, which would only display members who belong to a ...
0
votes
2answers
31 views

Using TODAY function with QUERY

I am using this formula (see below) to try and retrieve dates automatically between the current date and the next. However, I keep getting the error, "QUERY completed with an empty output." =...
0
votes
1answer
12 views

Pull unique values from other sheets as new sheets keep getting added

I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...
1
vote
2answers
20 views

Searching a list and replacing value if any item matches any item in the other list in Google Sheets

I have a list of usernames on two different sheets. I need to scan the first list of usernames to see if they appear in the second, and if they do, replace them with a predefined username. I can't ...
-1
votes
2answers
15 views

Value of Cell A1 could point to values of different cells

I am having difficulty with the following. Cell A1 could have values from 1 to 50. I have a listing on a separate table of different values that 1 to 50 represents. (I.E. 1 equals 1.68) I would like ...
1
vote
2answers
38 views

Remove Count column by repeating rows in Google Sheets

I have a worksheet, 'wi', in a Google Sheet similar to the following example but with an undetermined number of rows: Count | Entry | Description -------|-------|----------------- 2 | ...
0
votes
2answers
27 views

Consolidate columns from a dynamic range into a single column in Google Sheets

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range ...
0
votes
2answers
28 views

Showing top 5 most frequent values and counts of each from a data range

I have a Google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending ...
0
votes
2answers
21 views

Mark all line if checkbox is true

I will give an example: I have checkboxes in B3:G3. This is a checklist, I want to change the background color to green for A3:H3 if B3:G3 boxes are checked. Can someone please tell me how to do ...
2
votes
2answers
22 views

Multiple IF-statements in a single cell - checking long list of results [duplicate]

I have a Google Sheets where I enter all the results of my beer pong matches with friends. I'd like to see my amount of wins/losses with certain friends. This can be done with =IF(AND(B2="Won",E2="...
-1
votes
3answers
13 views

How do I sort data by size and not in ABC order

I am new to Google Sheets and have only used Excel a little, so Google Sheets is a bit different. I want to sort our band uniforms in size order from smallest to largest but Google Sheets only sorts ...
0
votes
2answers
46 views

IF and IFS not working properly

I am trying to locate data present in different sheets. I have tried using IF and IFS. Below are the formulas I have used. For IF, I get NOT IN USE even when I know there should be one of the sheets ...
-1
votes
0answers
11 views

How do i implement importrange the formula below? [on hold]

can anybody here share with me how to actually implement importrange into this formula.. =SUMPRODUCT(SUMIF(INDIRECT("HON_"&Parameters!$B$7&"_"&YEAR(Parameters!$A$2)&"-"&MONTH(...
1
vote
1answer
19 views

How to countif with dates before today's date that auto updates

I am trying to use countif to count a certain number prior to today's date. So if today's date is 8/15/2019 the formula would count the number that is the B column that had a corresponding date prior ...
0
votes
1answer
24 views

Sequential numbers via formula - Google sheets - IF & MAX

I use this formula to find highest value in column A of other sheet, and then add 1 to it to get new unique number for a record ID, and I use... =if(B3<>"",max(Backend!A3:A)+1,) with this ...
1
vote
1answer
26 views

Extracting specific phrases from a cell and echoing them in another

Longtime learner, first time poster here! I was wondering if anyone knew how to extract specific data points (phrases) from a Google Sheets cell and echo them in another cell. For instance, in my ...
1
vote
1answer
13 views

How to generate a range of serial numbers by formula?

I want to produce an array of numbers from 1 to n, where n is a value in another cell. So if the cell value is 10, I want to generate an array of the numbers 1 to 10. I found this resource which ...
1
vote
1answer
37 views

Google Sheets - Split row into multiple rows

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

Conditional row sum

I'd like to sum a column of cells if the corresponding cell on the next column contains a string of any sort. I've tried using SUMIF but I can't figure out how to combine it with ISBLANK or ISTEXT. ...
0
votes
1answer
27 views

Help with complicated math and syntax

I am trying to create a calculator for this mobile game that I play (Lord’s Mobile) but cant seem to figure out the formula. 1) In this game, you can get your upgrade time reduced when your guild ...
1
vote
1answer
61 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 ...
1
vote
0answers
11 views

Survey Data & Google Sheets: Excluding Missing Values?

I am using survey data in Google Sheets with rows for "before" and "after" responses, with the same survey administered to the same subjects at two different time periods. With each question, a number ...
1
vote
1answer
22 views

How to convert strings separated by comma from base 36 to decimal

I'm getting input from a google form. An associate scans a QR code giving a string like ^12t34:1 Cell B1 = ^12t34:1 The string between the ^ and : is base36. I can use C1 =DECIMAL(SUBSTITUTE(...
0
votes
0answers
33 views

Google Sheets Form Response Split Multiple Responses

I've created a form for my staff to enter metrics about their job so I can track how new operations affect their output. For the ease of my staff, I'm hoping that they can submit multiple responses ...
2
votes
1answer
27 views

Pasting text without interpreting as formula

I'm trying to copy a table of data from a website into Google Sheets. The table includes a column whose numbers are preceded by +/-, e.g., +/- 33. Excel works just fine, allowing me to paste in the ...
0
votes
1answer
20 views

How can I extract specific information from the cells?

How can I extract just the: Brand, Showroom Only Product, etc.
0
votes
1answer
22 views

Having trouble outputting GOOGLEFINANCE data

My wish is to create a table in Google sheets looking something like this: price day A B C D ... Z 1997-01-02 132.54 . . . . 1997-01-...
0
votes
2answers
29 views

Is it possible to have Google Sheets query function that accepts a dynamic query argument instead of hard coding?

I have a long OR query function that I will be reusing many times in many contexts and that may change frequently. Following the principles of DRY design, I would like to write this query once and ...
1
vote
1answer
13 views

Total Spent Formula in Google Sheets

So with my sheet, I am currently in the process of adding more stats in, I have looked into adding these stats by using Index & Match however it will only match to the first correct match it has ...
0
votes
1answer
14 views

Google Sheet Calculation Only Updates on Return

I am using the formula below. It will only update when I go into the cell and push "enter" to leave the cell. I think it is due to the iferror function. It is calculating correcting but I have to hit ...