The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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

reference cell as sheet in criteria in countif formula

I have a countif formula that gathers information from another sheet. =SUM(COUNTIFS(test!A:A,{"Story","Task"},test!D:D,{"In Progress"})) The sheet is named test. Is there any way I can write test ...
0
votes
1answer
20 views

Conditional formatting formulas in Google Sheets

I want to use Conditional formatting so all of the cells in column A change colors whenever the matching cells in column B have the word "later". ex: If cell B20 contains "later", then I want A20 to ...
1
vote
2answers
41 views

Conditional Highlight: color change cell only if multiple others filled in

I have a shift sign-up sheet in which I would like the cells in column A to be highlighted only once there is a value (name) in the corresponding cells in columns B:D. In this example, I would like ...
0
votes
1answer
301 views

QUERY with Week Number

I use query to extract two columns from another sheet with data. Column A has dates Column B - numbers I want to extract only rows that correspond to a specific calendar week number. For example 19. ...
1
vote
1answer
34 views

My formula in Google Sheets doesn’t add up to the same total in Yahoo?

Link: https://docs.google.com/spreadsheets/d/1KcYqRRqxZ3GWNAIUWgg9AGraTAzkrVxkDrk4ZsI5axY/edit?usp=sharing So I do sports spreadsheets for fantasy sports. I’m filling out my baseball spreadsheet ...
1
vote
1answer
25 views

How can I comment out parts of a formula in a Google Drive Spreadsheet?

How can I comment out parts of a formula in a Google Drive Spreadsheet? In PHP you can comment a line of code using a # what is the syntax to do the same in a Google Spreadsheet? I want to use this ...
2
votes
1answer
24 views

IF Cell = True, then retrieve (separate) Cell Value

Cells: T2 = Price to Pay U2 = (Tick box) V2 = =if(U2=True, "Paid", "To Pay") W2 = empty What I want to do is be able to tick U2 and have W2 contain the value of T2. V2 works, so it does not ...
1
vote
1answer
33 views

How to output end task time base on start time, duration and working time in Google Sheets

I'm using a spreadsheet to perform calculations and I'd like to make a task management file. For example: Working Time: 8:00 ~ 17:00 Lunch Break: 12:00 ~ 13:00 +- Start Time -+- Duration -+- End ...
0
votes
3answers
396 views

Remove duplicate items from array?

What would be the best way to remove duplicate items from cells in rows? For instance... Category 1 | Item 1 | Item 2 | Item 2 | Item 3 Category 2 | Item 3 | Item 1 | Item 2 | Item 3 ...
0
votes
0answers
17 views

Google Sheets Array formula

=ArrayFormula(IF(ISBLANK(Input!$B4:$B)," ",(INDEX(Input!A4:A, SMALL(IF(COUNTIF($A$3,Input!$B4:$B), MATCH(ROW(Input!$B4:$B), ROW(Input!$B4:$B)), ""), ROWS(A6)), COLUMNS(Input!B4:B))))) The result is ...
1
vote
1answer
19 views

LOOKUP for data from other sheet

So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail. So what I'd like to do is, ...
0
votes
2answers
98 views

How to use IMPORTRANGE when I have multiple tabs?

I have to export some data from a Sheet with 30 tabs(a tab for each day of the months) and I have to extract data under specific criteria from all the sheets which have to be done every month on a new ...
0
votes
1answer
36 views

How to express a range of values with nested if loops

This is a nested if loop I wrote in Google Sheets: Whenever I enter a value for B5 between 0 and 1, like 0.5, it says there is "no match". I think there is something wrong with the last section of ...
32
votes
2answers
58k views

FILTER(condition OR condition) syntax?

I am using FILTER() to display a value from Sheet 2 where the Name columns match. My problem is where I want: =FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal") as OR results ...
0
votes
1answer
18 views

Query Function Not Putting Consolidated Data in the Right Place

So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
1
vote
2answers
53 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 ...
0
votes
1answer
683 views

Can I use conditional formatting to highlight a cell for dates less than 30 days away?

I have a large spreadsheet where I need to keep track of coming expiration dates and I would like dates which are within 30 days from today's date OR which have already passed to be highlighted in red....
2
votes
1answer
766 views

Aggregate results by week and month for charts

Is there a way to convert daily values into weekly averages for charting? Sample source sheet --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
-1
votes
1answer
20 views

How to paste a comma separated string into one column in Google Sheets?

For example, if I have (bob, china, bing, bong). I want to put bob on top of china on top of bing on top of bong.
1
vote
1answer
67 views

How can I track the time it takes for someone to fill out a Google form?

I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
1
vote
1answer
254 views

Change text case so that first letter of the sentence is capitalized

Want to automatically change text case as I type on Google Sheets so that first letter of the sentence is capitalized.
0
votes
2answers
51 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 ...
0
votes
3answers
4k 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 ...
1
vote
1answer
554 views

Range notation and current row

I wonder how does work the following and is it documented somewhere? I have the sheet: A B ----------------- 1 2 3 =$A:$A 4 5 =$A:$A For column B (row 3 and 5) I see ...
0
votes
1answer
15 views

Multiple IF(AND) statements to determine Pass/Fail

I'm using Google sheets to try to generate a Pass/Fail in a cell based on two different parameters; If (R2) is less than 85% AND (I2) equals "T1","T2, or "T3" I need a fail generated. similarly, for ...
0
votes
1answer
12 views

Counting with Query

I am using queries, in order to create a dashboard, =query('Usuarios icorp'!I2:M522; "count(F) when F contains '"&B3&"' ", -1) I am having issues with my query function. I want to count how ...
2
votes
3answers
36 views

How do I get the address of a range with a formula?

Let's say I have a range called WidgetTypes in cells C5:M5. I want my formula in cell A1 to return C5:M5 or $C$5:$M$5. What formula goes into cell A1? cell("address",WidgetTypes) returns $C$5 but I'...
1
vote
1answer
36 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 ...
0
votes
2answers
36 views

COUNTIF to display which results meet criteria

I'm working on a spreadsheet, where I'm using COUNTIF to check the number of times a sheet range meets a criterion. This works. I'd like to see which cells (their contents/locations) which met the ...
-1
votes
1answer
223 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 ...
1
vote
2answers
96 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 ...
0
votes
2answers
24 views

Add columns based on rows

I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet. I have no idea if this is even possible, I've tried googling it. Might be ...
1
vote
1answer
25 views

“Calculating formulas” in Google Sheet disaster

I have a worksheet with 13 sheets. the largest one has 2000 rows. I have already deleted all the possible blank rows i could. I have also read all the guides on how to make a Google Sheet faster and ...
0
votes
2answers
257 views

How to group data (by row and column) in a Google spreadsheet?

I have data as follows: Name | Value | Y/N | Value | Y/N A | 10 | Y | 12 | N B | 100 | Y | 120 | N A | 20 | Y | 10 | Y B | 200 | N | 120 | Y C | 1000 |...
1
vote
1answer
20 views

How to calculate the following mathematical expression in Google Sheets?

I am trying to calculate the total money that can be made from dollar cost averaging every year using Google Sheets. For example: how would I evaluate this?
-1
votes
1answer
260 views

IMPORTXML error - empty table

I am trying to import a table from the lower part of the following website to Google Sheets: https://vistacapital.com.br/ I used the formula: =IMPORTXML("https://vistacapital.com.br";"//*[@id='sos-...
0
votes
1answer
29 views

How to apply scripting only to rows with data in them?

I have a Google Sheet that adds multiples rows per day as forms get entered. What's the best, most hands-off way to apply formulae to rows as they get added? I've built a script, but it applies the ...
0
votes
1answer
65 views

Alternate to Nested IF statements

Goal here is to have the sheets search a row, then once it finds the first instance of >=0, return the cell value that contains the month+concatenate the value in the cell next to it (or any other ...
0
votes
3answers
117 views

Checkbox to activate a column in QUERY

Is there a way to activate/deactivate a column in QUERY function? I have a query to query and selection of several columns Select Col1,Col31,Col32,Col33,Col25,Col26, and want to have a checkbox for ...
0
votes
2answers
68 views

IF Function with too many arguments

I want to apply this formula =IF(A2= "", "", IF(REGEXMATCH(A2, "Brand"), "Brand", if(regexmatch(A2, "Loans"), "Loans" , if(regexmatch(A2, "Cards"), "Credit Cards", if(A2, "Generic"),"Generic",...
1
vote
0answers
31 views

Insert image to spreadsheet from google drive folder, png image dose not show, why?

I have an image in a folder that I want to import into my spreadsheet (in a cell). I'm using the script from Insert image from Google Drive into Google Sheets (with some modifications). But this ...
0
votes
2answers
11 views

Opposite of SWITCH function? (i.e. enums in Google Sheets)

In programming, I could use a switch/case statement to do something like this: func animalNameFromType(animalType: Int) { switch animalType { case 1: return "dog" case 2: return "cat" ...
1
vote
1answer
59 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. ...
0
votes
1answer
17 views

If checked, add row to data chart?

Basically, I have been working on some automated solutions to show the date and data for the most recent 4 weeks on this data sheet. There are still some bugs to work out, but the last thing I need to ...
0
votes
2answers
151 views

Using a checkbox to auto-populate a row on another sheet

I have this spreadsheet which is used for receiving and recording work. SST responses sheet is where people submit requests, Workload is where we record all work. I'm wanting it so that when someone ...
0
votes
1answer
235 views

Increment number in array formula based on a condition

I have a data set with Sub-Total rows at various intervals. As the data can have rows inserted or deleted, I am trying to use an array formula to give each group of data an incremented integer as a ...
0
votes
1answer
26 views

Is there a better way or more efficient way to write this IF statement? [duplicate]

I need a way to get the return number to increase by "1" after every increment of "10". Is there a more efficient way to do this other than: =SUM( IF(AC11<10,0, IF(AC11<20,1, IF(AC11<30,2, ...
0
votes
1answer
10 views

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

So pretty simple thing I am doing, pulling data from a form and putting it in a template. =('Form Responses 1'!B3) is what I am doing, which works fine for my needs, however when I copy and past ...
0
votes
1answer
231 views

Calculations which won't recalculate

I'm trying to make a simple calculator in Google Sheets with checkboxes for certain values. For simplicity I'll refer to these Checkboxes as A and B. What I would like to have is the user selects A, B,...
0
votes
1answer
193 views

I want to get rid of google finance disclaimer

I want to get rid of Google finance disclaimer. I used the =GOOGLEFINANCE formula in a sheet. It triggered the display of a disclaimer at the bottom of the sheet that says: Quotes are not sourced ...