# Questions tagged [formulas]

Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.

1,895 questions
Filter by
Sorted by
Tagged with
4 views

### Google sheet ROI formula not working

My google sheet formula isn't working as intended. In order to calculate the ROI of an event im making I use different stats and make a big calculation of points at the ROI collumn [![Presentation of ...
26 views

### Sum multiple currencies by looking up conversion rates based on multiple cell values

I'm trying to sum values that are in different currencies. The currency for each value is specified in the previous column and the final result should be in Australian dollars AUD. I use a fixed ...
• 3
31 views

### Join results of a BYROW function that expands into multiple rows

I have two sheets, one containing user-entered data, and a second containing a bunch of data. I have included example sheets at the bottom of this question. I'm trying to build a formula that for each ...
• 215
16 views

### Sorting by date on a master spreadsheet with data imported from other spreadsheets

I have 4 spreadsheets importing data from cells A2:V, each spreadsheet auto sorts on edit by date (column 17), but when I ask the master sheet to auto sort by column 17, it sorts them by date but also ...
27 views

### How for each cell in a row return and concatenate multiple matches without dragging down formulas?

I have a list of items and in another place I have list that covers some of these items each time with a different key. How to fill in column B all matching results next to the original item (column A)...
• 1,230
25 views

### Growth between 3 cells in Google Sheets

I am creating a data wall to compare student achievement across 3 time periods Beginning of the Year(H2), Middle of the Year (I2) and End of the Year(J2) and having the difference recorded in K2. I ...
35 views

### How to use the value of a cell to find another cell?

I’m trying to create my own data sheets for reading at school but when I put the reading ages in, I need it to select the zpd level and the reading targets automatically base on what I input. Eg if I ...
36 views

### How to use Array with Sumproduct or other formula

I am trying to use an array formula like SUMPRODUCT to perform a lookup with 2 criteria. I have a sheet named data that stores customer-specific pricing for products. A B C             D 1 ...
46 views

### Formula for adding value in each cycle for compound interest

In Google Sheets, I know the formula for compound interest, for example: 10000*(1+10%)^n 10000: start-up cash 10%: interest rate n: interest cycle But what if I also add 10000 cash each month? What ...
1 vote
25 views

### How do I set up conditional formatting for values that contain text from a cell in another tab?

From similar problems I found online, I've tried a number of regexmatch and combined it with indirect but nothing seems to work. Here's a sample of my sheet: https://docs.google.com/spreadsheets/d/...
1 vote
35 views

### What is the syntax for an indefinite range? [duplicate]

In Google Sheets formulas I often see ranges specified with a large terminating number, such as B3:B299 = SUM(B3:B299) The upper-bound, or largest row number, is often chosen to be large enough to ...
70 views

### Google Sheets Formula to find specific months

I have a Google Sheets document with multiple sheets. What I want to do is perform a lookup on a specific sheet Expenses, and find all the expenses for a given month and year. Here's what I have so ...
• 31
33 views

### How to strikethrough a text if the text is the same in another cell using conditional formatting?

I am making a table assignment for the guests for my brother's wedding. I want to know if it's possible to conditionally format to strikethrough a cell that has the same text as another cell. In this ...
93 views

### Automatically copying the formula whenever I INSERT rows IN BETWEEN rows, not at the end of the row

What I want to happen is that whenever someone insert a new row in between rows, the formula would have also copied there instead of dragging the column again and again. Here's the code that we're ...
94 views

### Return the most recent unit price of a matching item that is dated on or before a specified lookup date

In Google Sheets I have a list of orders recorded by order date, item, and price. There is one order per row, but the same items are ordered on different dates and the prices vary between orders. I'm ...
• 31
81 views

### How do I exclude a text value from a count in a Google Sheets query statement?

I have gotten an output that works for my sum, but I need to exclude one value in count(D). I want to exclude from the count in column D all cells containing the text ZERO. I've been researching and ...
60 views

### COUNTIF that uses Dropdown Values in the Formula

I am trying to create a Google Spreadsheet to keep track of how many shirts I need to order, for a Cross Country Camp. I wanted to use a COUNTIF to count how many of each type of shirt I need to ...
44 views

### Conditional formating max values over multiple rows

I have Google Sheets data in column pairs from Column B to AQ. Each column pair represents a single year with the first column containing order counts, Orders, and the second containing order totals ...
• 21
2k views

### Converting "4.8 million" into an actual number

I have a sheet that has numeric records that are a mix of three different number types: 260,000 4.8 million 870 million 1.1 billion The first one is handled just fine as a number, but the ones ...
• 151
366 views

### Formula to count rows between positive values

I need a formula in Google Sheets to count the number of rows between each positive number in a column. In Column B of the table below, for every value in Column A that is less than, or equal to zero, ...
22 views

### I wanted to set the maximum value of each unique input as active

In the example data below, for each unique SYS Name, the Status column is marked Active in the row with that SYS Name's highest Serial Number. In Status, Column C, for each unique Sys Name the row ...
151 views

### alphanumerics in duration in sheets - how to convert to a number of minutes

Working in google sheets, I am getting input from another program in the form of "Xh Ym Zs" to track time. I'm trying to write a formula that will just yield a number of minutes, so "2h ...
• 21
1 vote
33 views

### How do I make dropdown selections add value to a totals column in Google Sheets

Very new to Google sheets and very lost. I am trying to create an awards tracker for school Merits 2024 Ideally, I want to include drop down menus with options for each week we hand out merits at ...
• 13
1 vote
43 views

### Formula to calculate the sum of consecutive 0's and -1's

I am hoping someone can help me with a Google Sheets formula to calculate the minimum sum of consecutive 0s and -1s in a column. 0s and -1s are consecutive until interrupted by any other integer. For ...
• 13
1 vote
24 views

### Formula to return a value when the adjacent column's text contains any value from an array of text strings

In Google Sheets I have an array named "Tech_issues" with the following values: Array name value 1 value 2 value 3 value 4 Tech_issues crash froze glitch error I'm looking to create a ...
22 views

### Google Sheet formula to search all columns that match a specific keyword and sum values from them [duplicate]

I'm trying to paste a formula on cell AS2 that searches the column names on Row 1 for an occurrence of a keyword and if that column has such a keyword on its title searches Row 2 for numeric values ...
• 101
67 views

### How to concatenate all further columns from duplicated items of a first column?

In Google Sheets I have two or more columns of data: Column 1 is a list of items that may include duplicates. Column 2 (and any additional columns), contains keys each associated with the item from ...
• 1,230
27 views

### Google Sheets Auto Categorize Budget from Statement

I have a Categories sheet and a Statement sheet. The Categories sheet contains a list of all possible categories, and the Statement sheet is an export of my credit card. How can I automatically ...
67 views

### Multiply/Concatenate String Arrays

Suppose I have three columns importing data from elsewhere, like so: Name Company Division Adam Meta Ops Betty Nvidia Sales Carl OpenAI Development The number of values in each column may change ...
• 23
37 views

### Absolute cell references not working as I'd expect, what am I doing wrong

I am working on a sheet that tracks yearly budgets to review/see trends in the data. I am trying to make it user friendly and essentially "dummy-proof" for lack of a better word. Most of the ...
51 views

### How to use a cell's array value

Simple question, but I can't figure it out. In the spreadsheet above I have a table in A1:B5. I have a filter in A7 and its value is an array. In A11, I want to textjoin all the values of the filter ...
12 views

### Grouping or linking data I added on a sheet to its corresponding filtered data brought over from another sheet

The small towing company I work for uses Google sheets for our calls logs. I filter logged calls so that all police calls are copied from the "General Log" sheet and show up on a different ...
• 9
1 vote
38 views

### Combine two SUMIF formulas into a single formula

I have two SUMIF formulas. Both do what I expect them to but I need to combine them together. The first formula gets all values from Column I where a specific word (eg "Word") is written in ...
• 125
50 views

### How do I use google sheets to remove items as they are selected from a list that may include duplicates

I would like column C to be column A with everything in column B filtered out. Column A may have duplicates and those should carry over into column C, unless the same number of that item appears in ...
57 views

### How to sum across columns automatically?

Would like your help to solve the problem as file attached. https://docs.google.com/spreadsheets/d/1E3KTOYHuYIJy9uQKsvlXwbCpwL6cU1IHAQ-Yirb-AfQ/edit#gid=0 I would like each column to sum automatically ...
1 vote
47 views

### Generate formula programmatically

I am trying to generate a large number of formulas programmatically. I can easily generate a formula text doing something like ="=1+1" (in reality it will be a bit more complex). This will ...
• 113
1 vote
23 views

### IF OR conditions & FILTER in google sheets

This is what I am trying to accomplish: If column A = "blue", OR column A = "red" AND column B = "sold", get sum of column C Here are my attempts: =SUMIFS(or(C:C, A:A, &...
43 views

### What's the difference between using "<>"&"" vs. "<>" in Google Sheets formulas?

For Google Sheets, I've seen people use both =COUNTIF(range,"<>"&"") as well as =COUNTIF(range,"<>") to count the number of non-empty cells in a range. Is ...
• 41
30 views

I am creating a sheet in google sheets to look like a dashboard from the below data. I am asked to create this with formulas. I am getting the filter part correct. But changing the values from filter ...
1 vote
20 views

### Hide a value in a cell unless another cell has a given value

I'm trying to create a formula to generate a list of values in a dropdown. Each row contains a task that has a status, and a priority. The priority is set from a dropdown containing a list of numbers ...
49 views

### Googlel Sheets formula to SUMIFS with multiple AND and OR criteria

I have a dataset similar to the image below. I am trying to calculate the sum in column A if: Region (G) = NorAm AND Team (H) = Corporate AND Stage 7 (F) <= 2/1/24 where either of these conditions ...
• 43
45 views

### Relative cell reference in formula doesn't change as expected when copied to a different cell

I have a working IMPORTXML formula in Google Sheets that includes a relative cell reference. When I copy the formula to another cell, I expect the relative cell reference to update but it does not. ...
128 views

### Formula Suddenly Stopped Working

I have a Google Sheet which requires time to be submitted for different people. I recently had a SUMIFS on another sheet adding the time together based on today() function. The formula I was using is ...
27 views

### Combine Columns from Another Sheet

I'm very new to using Google Sheets, and I'm trying to create a sheet that pulls data from my form submissions. The form submissions go into Shift Report and I have two outcomes depending on product. ...
1 vote
34 views

### Count cells with an integer in them as substring

Most cells in the column have a single integer in them, e.g. 5. Some cells have a range as a string, e.g. '4-5. (Let's ignore the problem where someone types 4-5 and the app interprets it as April 5th,...
• 175
1 vote
27 views

### Formula that will duplicate certain cells (2 ranges of cells) in a row & place in the next row according to a # in a cell from 2-20

It sounds confusing. I am entering futures trades in a log. Sometimes those trades (like one trade) is/are copied multiple times and taken all at once in groups or batches/same trade but several ...
• 11
1 vote
183 views

### Is there a function similar to Excel's "Evaluate Formula" to help debug complex Google Sheets formulas?

In Excel there is a button to "Evaluate Formula", so I could step through whatever formula I wrote and find where I messed up. Is there anything equivalent in Google Sheets? EDIT: I am not ...
• 139
1 vote
27 views

### Why is cell that contains TRUE not equal to "TRUE"?

Here's my formula: =AND(M2= "FALSE", N2= "TRUE", O2= "A",P2= "No") And here's the sheet. My value returned false. What do I do?
• 11
1 vote