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

Trying to calculate how frequent of the shift happen in certain criteria

I work on schedule, would like to calculate how many are morning shift and how many are afternoon shift base on market. I've tried if with countifs, arrayformula with countifs, it doesn't seem to work....
0
votes
1answer
28 views

How to find _any_ formula error in a Google Sheet via a standard command (and not a script)?

For complex Google Sheets with many tabs (my teams effectively employ Google Sheets as a sort of database in these scenarios), is there a relatively-easy way to search for any and all formula errors (...
1
vote
2answers
27 views

How to prevent row/column insertion/deletion from breaking links to other sheets?

Earlier I asked this question: How to create a link with content bound to other cell's content in Google sheets? And until now the provided solution was working perfectly for me but now, the ...
1
vote
1answer
25 views

Trying to add multiple formulas in one cell

I'm very new to Google Spreadsheets and I'm trying to add multiple formulas in one cell. This is my code so far, I'm not very good at formulas. ={SUBSTITUTE(C:C,"wd","Web Development&...
0
votes
1answer
44 views

importrange & query

I don't have programming background, but as a novice, experiment with formulas on my own. I have two separate live sheets; On the 1st sheet (sheet A) tab F, when I write Y into any cell in Column O, ...
0
votes
0answers
22 views

How to convert multiple form responses into easily readible table to use

I have a Google Form that is going to be used by different employees to add stock received. Since there is usually more than 1 item being received, I needed to provide space for them to add multiple ...
0
votes
2answers
52 views

Google Sheets for if contains “*”

I have found the the regexmatch solution for substring: Google Sheets formula for "if contains" However, why for "*" is not working? I'm trying to make a condition if the name ...
3
votes
1answer
51 views

Using regex to select the text between two characters IF there is text

I have this text: <a class="_f5rco33" href="/account-settings"><div class="_ojs7nk">Account</div></a> And I want to grab the word "Account&...
1
vote
1answer
26 views

Locking a formula when adding rows

Currently I have this formula in there =IF('Form Responses 3'!G$20="","",COUNTIF('Form Responses 3'!G$1:G$167,F20)) It does what I want it to do if I go in a tweak it later, but I ...
1
vote
1answer
23 views

Replacing items in comma delimited cell with same string each time

I'm trying to create a formula that will replace any contents of comma delimited cell with single string. The number of items in delimited cell will vary... Specifically: 524352435,2345235523,...
0
votes
1answer
8 views

Excel formula to skip the rows with zero value while linking

I need a small help in fixing my formula. i have a report from erp with few employees name and the amount they owe. It is a dynamic report and changes everyday. My requirement is that I need a formula ...
0
votes
2answers
24 views

Removing specific text from a box?

Is there a way to remove text from a box in google sheets? Here is a example: Total: [=sum(stuff here)] User 1 5 points User 2 8 points The [5 points] is one cell. If I just try to sum it up, it ...
1
vote
1answer
23 views

Attempting to use a LOOKUP with an Importrange tab on Google Sheets to make sure values across two sheets are correct, but I keep getting errors

This is the sheet I am working on. The trouble starts on the tab titled "Sectors and subsectors", on row 56 under the column named "checksum". What I am attempting to do is as ...
0
votes
1answer
26 views

Google Sheets - Filtering Chart Data with Checkboxes

In Google Sheets, I'm making a 'workbook' that keeps track of inventory in a shop. There are 3 sheets in the workbook. The first one is the raw data which is 'Item Name', 'Initial Stock', 'Stock Used',...
0
votes
1answer
18 views

Strikethrough name in cell in specific range if it contains/matches name from another cell range on same sheet

So we've got a calendar on google sheets, it has a bunch of different things besides just the days but the part I'm working on is easily displaying who is not going to be working that day. We have a ...
2
votes
2answers
64 views

How do I find the max value in a column only if it contains certain texts in google sheets?

The data has numbers in text format and texts with numbers. How do I find the max of the numbers in text format without converting to numbers? How do I find the max if it contains certain texts (ie, ...
0
votes
1answer
29 views

Google Spreadsheet Advanced Conditional Filter

Let's say I have a money "Transactions" Table with columns: [Timestamp, Account, Category, Inflow, Note, Group]. I would like to use FILTER() function to display it in another page with 5 ...
1
vote
1answer
39 views

Google Sheets Insert Cell Values into another Cell using formula

So, I am looking to insert two cell values into one cell, one following the other. For the following example see the Image below. I want to put a Formula in C such that the formula would result in ab ...
1
vote
1answer
50 views

Use Regular Expressions to Add Text to Beginning or End of Cells

I have a bunch of words in a sheet in one column, e.g.: Study Guide 101 Meditation for Beginners Cheatsheet Lose Weight Fast What I want to do is add a word to the end of these in the cell using ...
1
vote
1answer
42 views

Query Function - remove parts of entered term and carry out formula

I'm sorry if the title is confusing. In the List to Search tab, I've got a bunch of orders. What I do is select the dishes in a single cell in this manner: And I want to just dump them into the ...
2
votes
1answer
72 views

Conditional Format if cell contains partial match for text

I want to conditional format the columns B to G, if one of them contains the text in say, cell H5. This is the formula I use for that, =$H$5=B1 at the moment. What I want is for the conditional format ...
1
vote
2answers
57 views

Google sheets, convert value from 1 column to another based on particular data in it

Let's say I have those values in the column: w 1 w 2 w 3 I want to convert those values to 1 2 3 in another column. So basically I need to convert the value "w x" to "x". How ...
0
votes
0answers
31 views

Conditional formatting with multiple conditions (lost between IF or AND, and not empty cells !)

I am trying to combine formulas here, to put color on specific cells (out of a specific range) when they are not empty, and when a checkbox in another cell is checked. Here is what I tried : =AND($D4=...
1
vote
1answer
107 views

Search multiple columns for one value

This is roughly what my table looks like: A B C D E F Name |Ingredient 1 |Ingredient 2 |Ingredient 3 |Ingredient 4 |...
0
votes
1answer
21 views

Conditional Formatting Based on another Cell with Multiple IF's

I had a Conditional Format in Place, but it somehow stopped working once I filled in an IF Argument into my cell. A1 Contains a Number I put in manually A2 Contains another Number I put in manually A3 ...
0
votes
2answers
39 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 ...
0
votes
0answers
3 views

Using a cell to build a formula

I am trying to build an ever changing formula, that will list the contents from multiple work sheets. The trouble I have is sheets will be constantly deleted and added to the spreadsheet, meaning I ...
0
votes
1answer
16 views

How do I use text and a formula in a cell

"Books Added in October 2020 " & "counta(A3:A)" This is not working. Can someone help me?
0
votes
1answer
32 views

If colE contains value then import colAY from same sheet

I am trying to import 2 columns from one sheet into another but trying to make sure I import only the cells from col2 when col1 has a value (all different values). I imported my first column needed (...
0
votes
1answer
41 views

Convert weekday to text output

In a monthly spreadsheet I have cells which contain the year (A1) Month (A2) and then the rest of the column running from cell A4 through to A34 numbered as the day in the Month (1 to 31). My ...
0
votes
1answer
35 views

Use array as function parameters

Given a function that returns an array, is it possible to "spread" the return values as the arguments to another function? For example, I'd like to pass the result of SPLIT("example.com,...
1
vote
1answer
151 views

How to show Day of Week in Order by Day of Week in Google Sheets

I have the following formula that displays a total by Day of Week (weekday) from a date in J and amount in K Example J contains 1/1/2020 ... until 9/29/2020 K contains numeric value 123 ... 1999 =...
0
votes
1answer
24 views

Any way to duplicate a formula that needs individual relatively absolute references?

I need to duplicate a formula down for many rows. However each one needs to be an absolute reference, referencing a specific cell in that individual row. I can't just fill down the original formula ...
1
vote
1answer
34 views

How to create a link with content bound to other cell's content in Google sheets?

As stated in the tittle I'm trying to make the content of a cell that links to an other cell have it's display text be based on the other cell's content. Before I was able to do it by right clicking ...
0
votes
1answer
41 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 ...
0
votes
1answer
22 views

Google Sheet average a row from last 10 days to the current date

I am looking for a formula that would automatically average their own row on C3 to C10 based on the last past 10 days from the current day as seen on their right side. The list doesn't have the ...
0
votes
0answers
35 views

How to write a conditioning rule referencing another cell in Google sheets?

Problem: I want the color of the cell to change based on the value of another cell. I have a cell (B1) with MTD capture that is calculated by taking asset A and dividing it by Asset B in different ...
1
vote
1answer
26 views

Is there a formula to automatically populate invoice with only the products that have been ordered and excludes the products not ordered?

I have a range of products in my order data sheet and I need help creating a formula that automatically fills up my Invoice sheet with only the products that were ordered. Currently I can only ...
0
votes
0answers
30 views

Is it possible to use a cell containing date as data validation in query statement

Is it possible to use a cell containing a date (as data validation) in a query statement?
0
votes
2answers
69 views

How do I change the date to the coming Monday every Friday at 9:30 pm in Google Sheets?

I would like to write a function that checks what day of the week today is, and whenever it is Friday 9:30 pm it should populate the cell with the date of the coming Monday. So since today is Friday ...
0
votes
1answer
35 views

Multiple IF functions on a single cell

How do I add multiple IF functions on 1 cel, without blocking it on the first line. As you can see, column D contains a bunch of text. In column A, I want to categories them. It works up until the ...
1
vote
1answer
30 views

Use importhtml to import a single cell vs a whole column

As the title would read, I am looking for a method to import data from a retail site to my current spreadsheet to have the prices update in real time. Unfortunately I am not educated in this so I am ...
2
votes
1answer
31 views

Keeping a cell formula set to the same cell even when a row is inserted

I have one sheet that needs to pick up singles sells for a lot of other sheets. On the other sheets I have to change the information each month and I work across the rows so I insert a row at the top ...
0
votes
0answers
30 views

Putting multiple arguments/conditions

How can I put multiple arguments in a cell using Count IF in Google Sheet when I am providing separating the arguments/conditions I am getting error message?
2
votes
1answer
26 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:
1
vote
0answers
50 views

How to keep same protections/settings when making a copy of a Google Spreadsheet? [duplicate]

I have a spreadsheet that I want to create about 100 copies of at once, all copies with different titles. I found a script that allows me to do that, all I have to do is fill out information from cell ...
0
votes
0answers
75 views

How to separate direct sibling elements into different columns?

So basically I'm trying to import a list of values from a certain web-page into my spreadsheet and I'm stuck. I want to make a list of names with their corresponding number stages next to them, all ...
1
vote
1answer
151 views

Daily Average Calculations in Google Sheets

In the editable spreadsheet below I have a column of dates (column A) alongside a column of data (column C). I want to calculate the daily average of the data in column C and enter it in columns C and ...
2
votes
1answer
57 views

Compare number in one cell to multiple cells and give out nearest number

https://docs.google.com/spreadsheets/d/1Dew-CLxHvMW2BgI9axdJTEI-2CezTx7k6Vt1R48mwgA/edit?usp=sharing Above is the sample sheet. I need a formula in K cell

1
2
3 4 5
31