Skip to main content

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
0 answers
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 ...
Maslie's user avatar
  • 1
0 votes
1 answer
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 ...
Phil's user avatar
  • 3
0 votes
1 answer
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 ...
Spedwards's user avatar
  • 215
0 votes
1 answer
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 ...
Psych-out's user avatar
0 votes
2 answers
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)...
LWC's user avatar
  • 1,230
-1 votes
1 answer
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 ...
Justin Barry's user avatar
0 votes
2 answers
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 ...
Kevin Wilson's user avatar
0 votes
1 answer
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 ...
Anakin Teow's user avatar
-2 votes
2 answers
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 ...
Yu Lun CHEN's user avatar
1 vote
1 answer
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/...
Jessica Gutowski's user avatar
1 vote
2 answers
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 ...
Theodore Shepard's user avatar
3 votes
2 answers
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 ...
jbuck's user avatar
  • 31
0 votes
1 answer
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 ...
user326450's user avatar
0 votes
2 answers
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 ...
Zen_Zen_Desu's user avatar
3 votes
1 answer
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 ...
Se7en's user avatar
  • 31
2 votes
1 answer
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 ...
Larry OBrien's user avatar
0 votes
1 answer
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 ...
James's user avatar
  • 1
2 votes
1 answer
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 ...
Wes B's user avatar
  • 21
4 votes
2 answers
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 ...
RobKohr's user avatar
  • 151
3 votes
3 answers
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, ...
Yu Lun CHEN's user avatar
0 votes
2 answers
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 ...
Vintikon Indonesia Perkasa's user avatar
2 votes
1 answer
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 ...
user8531's user avatar
1 vote
2 answers
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 ...
MHLO17's user avatar
  • 13
1 vote
2 answers
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 ...
Will's user avatar
  • 13
1 vote
1 answer
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 ...
flipskyrain's user avatar
0 votes
0 answers
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 ...
MACC's user avatar
  • 101
2 votes
1 answer
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 ...
LWC's user avatar
  • 1,230
-1 votes
1 answer
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 ...
user325057's user avatar
2 votes
3 answers
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 ...
dee123's user avatar
  • 23
0 votes
1 answer
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 ...
cotimms's user avatar
0 votes
2 answers
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 ...
Hugh Beyer's user avatar
0 votes
1 answer
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 ...
Jann's user avatar
  • 9
1 vote
1 answer
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 ...
A.bakker's user avatar
  • 125
0 votes
2 answers
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 ...
KyAnSan's user avatar
-2 votes
2 answers
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 ...
Anakin Teow's user avatar
1 vote
2 answers
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 ...
Luca's user avatar
  • 113
1 vote
1 answer
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, &...
google geek's user avatar
3 votes
1 answer
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 ...
Kir_Mal's user avatar
  • 41
0 votes
1 answer
30 views

Counta and Array_constrain formula in Google Spreadsheets

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 ...
Ramya's user avatar
  • 1
1 vote
1 answer
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 ...
Christian Guest's user avatar
0 votes
2 answers
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 ...
Elaine's user avatar
  • 43
-1 votes
1 answer
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. ...
Michael Langhout's user avatar
2 votes
1 answer
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 ...
user avatar
0 votes
1 answer
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. ...
user avatar
1 vote
1 answer
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,...
Phrogz's user avatar
  • 175
1 vote
0 answers
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 ...
Denise's user avatar
  • 11
1 vote
1 answer
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 ...
Doragon's user avatar
  • 139
1 vote
1 answer
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?
Laura's user avatar
  • 11
1 vote
1 answer
24 views

Formula to check if a form is filled out each week

The spreadsheet we are attempting to construct using Google Sheets takes form responses across multiple weeks (which come from the same form) and checks to see if that form has been completed for that ...
Swarit Srivastava's user avatar
0 votes
1 answer
43 views

How to force reloading custom function result? [duplicate]

I use this script to get the current sheet name : function sheetName() { return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName(); } then use =sheetName() in the cell to load the ...
andio's user avatar
  • 397

1
2 3 4 5
38