Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
2,026
questions
-2
votes
2
answers
40
views
How do I copy names marked with an asterisk * from one column to another another?
I've got a list of student names in Column A. I need to copy the names that are followed by an asterisk * (I've highlighted them in the screenshot) into the * Seniors column D. Is there a formula I ...
- 1
0
votes
1
answer
127
views
Query Problem: Function QUERY parameter 2: NO_COLUMN: Col64
I have been using this formula in a document for quite some time and have had no problems with it. Now however when I opened it up, it suddenly stopped working. When I revert the document to an old ...
2
votes
1
answer
63
views
IF function condition with REGEXMATCH returns wrong result
The value of the cells are:
D
F
R
Marco Marques (+19091234567)
06/19/2023_Purchase: (Footlocker) Spiderman All Terrain Clog Size 12
win
The output: Not Counted
I don't understand why it returns &...
- 21
3
votes
1
answer
31
views
Can I sort cells in Google Sheets with a key function?
I have some data in a Google Sheets document that I would like to sort by computing some value from two columns and using that as the sort key.
Say my data is like this:
A
B
C
D
1
...
...
8700
5
2
....
- 131
0
votes
1
answer
35
views
Custom formula is not working in google sheet
I'm trying to add strikethrough style when a checkbox is check, but unfortunately, only one cell is applying the formula. Please see my screenshot below.
Screenshot
- 103
1
vote
1
answer
132
views
Google Sheet ImportRange embedded in filter with RegexMatch option
I have a complicated spreadsheet with multiple tabs that filter for various things. The raw data comes from a file that I must download as a .csv file each day. Looking for a way to simplify a bit.
...
1
vote
2
answers
44
views
Is it possible to do a more concise IF x = 1 OR x = 2 OR x = 3 in Google Sheets?
The following is a part of a formula I'm using to filter for week day rows (for conditional formatting):
OR(
MOD(ROW(),7)=2,
MOD(ROW(),7)=3,
MOD(ROW(),7)=4,
MOD(ROW(...
- 416
0
votes
1
answer
40
views
Formula to check is cells are blank. If the cell is not blank then assign a value
I have a data set on Google Sheets where for example,
Name
Stage 1 Date
Stage 2 Date
Stage 3 Date
Stage 4 Date
Bob
3/1/2023
3/5/2023
3/31/2023
4/15/2023
Joe
2/1/2023
Zoe
3/21/2023
4/1/2023
4/15/...
- 19
0
votes
2
answers
34
views
Automatic Numerical Incrementation [closed]
I'm trying to get automatic incrementation working for a dynamic range of data. I've got this convoluted formula that works in some of the cells but not others, returning a #Value error that doesn't ...
- 11
0
votes
1
answer
77
views
Is it possible to modify with a formula the same cell where you introduce the value in Google Sheets?
So imagine we have column A, B and C. And I want to sum A1 + B1
You can do type in C1 "=SUM(A1,B1)" and you will see the result in C1
But, is there any way we can introduce the formula in A1 ...
- 109
2
votes
2
answers
144
views
Sum of all numbers from many columns that match text from multiple rows in another column
I have some data from analytics that I need to combine somehow and trying to find the best way of doing this. I have data similar to the following:
URL
Sessions
/
59
/?variable=1
2
/?variable=2
37
...
- 135
1
vote
1
answer
66
views
SUMIF column by comparing two date columns
Please help with this formula.
=ARRAYFORMULA(if(A3:A$900="";"";IF(DATEVALUE(History!C$3:C$900)<D3:D900;SUMIF(History!A$3:A$900;A3:A900;C$3:C$900)*SUMIF(History!A$3:A$900;A3:A900;...
- 23
1
vote
2
answers
134
views
How can I link two addresses using a Google Sheets formula to create a Google Maps URL?
It seems that it is easy enough to get the URL to one address using the following formula:
=HYPERLINK(CONCATENATE("https://www.google.com/maps/search/?api=1&query=",L13),"MAP")
...
0
votes
1
answer
69
views
How to sum 5 largest numbers of 6 numbers across multiple sheets
I have a spreadsheet where we are keeping scores for golf across 6 weeks but we are only taking the best 5 scores from the 6 weeks. Each week (1-6) has its own sheet (tab)
I need to get the sum of 5 ...
- 1
-1
votes
2
answers
42
views
Subtotal of duplicate items in cell mentioning the duplicate items the last time
Currently, I am using the sum function to total matching item names by manually selecting matching cells. I would appreciate help automating the calculation of subtotals by item name.
Link to my ...
1
vote
1
answer
56
views
Trouble with a formula in Sheets to forecast shipment arrival
I have big trouble making my formula works and I am pretty sure that this is simple.
So simple that I am litteraly struggling on it for weeks now.. So I give up :'(
Let me explain:
I am using sheets ...
- 13
0
votes
2
answers
55
views
Return last value that isn't an error in each row
Trying to get some help with an issue I'm having.
In Google Sheets I have a data in columns A to D.
Column A contains my original list of names.
Columns B to D contain either a new name or an #N/A ...
- 1
1
vote
1
answer
3k
views
Formula to generate unique sequential IDs for items in a column that don't already have IDs
I am cataloging items in a spreadsheet using Google Sheets. Every item needs a unique ID, which is a sequential code formatted like ABxxxxxx, where AB000000 < ABxxxxxx < AB999999, starting from ...
- 13
1
vote
1
answer
31
views
Two Different Answers for the Same Simple Formula in Google Sheets
I have a spreadsheet with a very simple formula: =C4/D4
Sometimes, this very simple formula gives the wrong value.
Cell C4 contains the value 390062 formatted as a number with thousands separator
...
- 13
0
votes
1
answer
41
views
SUMIF sometimes not summing when criteria is met
SUMIF is not summing some of the cells despite the logic being met. For example, cell B9 on sheet 3 should result in 25, but it is coming back with 0.
I've tried
sumif(Sheet2!$B26:$O26, "*MW*&...
- 1
0
votes
1
answer
31
views
Google sheets. Compare the value in column on the 1st sheet with 2 others and then pull cells to y the right of matching value in the other sheets
I have one workbook, 3 sheets. Ultimately I'd like information from sheet 2 and 3 joined with corresponding information on the 1st sheet.
The 1st column has an ID number on each page. How can I make ...
Ariceliz
0
votes
1
answer
32
views
Pulling data from Google Sheets tab and placing them into new tab but into the correct section for that month
I have a Google Sheet containing sales information; this data is inputted into a sheets tab where staff list common sale items: Client Name, Date, Month, and Invoice Amount.
My current Google Sheet (...
- 11
-1
votes
1
answer
23
views
Two drivers, two calculations, one cell [closed]
I have a trucking business with two drivers. I receive a value ($) for each load my business delivers.
I pay a share (%) of that value to the driver of the load.
The share for "Driver1" is ...
2
votes
1
answer
70
views
LOG function in Google Sheets
I am trying to convert a function from Microsoft Excel to Google Sheets. In Microsoft Excel, it is written as =-cellA*LOGZ(cellA;2). The Microsoft help page points out that the syntax for LOGZ is ...
- 33
0
votes
1
answer
82
views
How to filter an spreadsheet using filters + drop down feature in google sheets
I've created an spreadsheet and I'd like to filter the information in the rows using a drop down filter. I've managed to create the drop down menu in A3 but now I'd like to show the information in the ...
- 11
1
vote
2
answers
331
views
When subtracting times in Google Sheets sometimes it fails and the value is incorrect
I have two fields in my Google Sheets spreadsheet and a third cell that subtracts the difference between them. Most of the time it works fine but sometimes it fails.
The way it fails is I believe that ...
- 177
1
vote
1
answer
332
views
Google Sheets MATCH/VLOOKUP formula searching Date with AM/PM "cannot find value"
I am trying to return a value based on matching dates that have AM/PM. I have tried Index/Match & ArrayFormula & VLookup Formulas that all should have worked to bring in the value that I'm ...
- 15
0
votes
2
answers
30
views
How to get data from table to another when you can't re-order columns
Having trouble putting this into words, reading countless threads describing issues similar to mine, but not this exact one. So here's a super simplified version of what I need done.
I have numbers ...
0
votes
1
answer
84
views
How to format time duration in a mixed string/number cell?
I have a cell that I would like to format as follows (and with the desired values):
3:15 / 48:00 (-44:45)
Here is what I have tried (attempting to the use the TEXT function):
=text(sum(B4:4)*24,"...
- 648
0
votes
1
answer
33
views
Leading with a '+' without starting a formula - but keeping it a number
This question is close to what I want, however the answers presented there all involve forcing Google Sheets to recognize the elements as strings. This solution does not work for me, however - the ...
- 103
0
votes
1
answer
215
views
Google Sheets: How to make a progress bar cap at 100% for a completion date, not show something like 462%?
I have created a Gantt Chart is Google Sheets, with 4 columns: Progress, Start, Days, and Ends.
The issue I am having is when a project is completed, the percentage shown is over 100%, which makes no ...
0
votes
1
answer
19
views
Can the formula of a cell be conditional based on the dropdown selection of another cell?
Is there a way to create a formula where D9 is included/excluded from the formula based on the answer to the dropdown selection of D13 (Financing Repairs? Yes/No)?
Spreadsheet
I've spent hours ...
0
votes
2
answers
14
views
Spreadsheet : find and edit a value by autocompletion on header row and column
I am using a Google spreadsheet as a 2x2 sparse matrix of permissions.
I have a header row "product name" and a header column "Employee name" (not necessarily in first position)
...
0
votes
2
answers
48
views
Google Sheets Formula Help needed - Vlookup/Index/Match
this is my first time on here, I'm hoping someone can help me :)
I have a formula that finds the final occurrence of a value in a range and gives me the date for that occurrence
=ArrayFormula(LOOKUP(2,...
- 15
0
votes
1
answer
4k
views
Need a cell to be BLANK - NO ZEROES - if no data is input into the FORMULA RANGE above?
I'm trying to make it so that a column of data sum when data is input into the column above, but remains BLANK if there's no data.
I've been able to accomplish this with AVERAGING, using this formula ...
0
votes
1
answer
461
views
All columns values in one column
I need some help! :S
I have a spreadsheet and three list of items (Column "A", "B" and "C") and I need to put all the values of all the columns in only one column (Column ...
1
vote
1
answer
18
views
Import values from others spreadsheets to a single one
I'm having some trouble to import data values from many spreadsheets to a single one. I need to import all the values from all columns "A" of each spreadsheets to the column "A" of ...
0
votes
1
answer
41
views
How do I check a set of numbers in different columns against numbers in different columns?
I am trying to make a Lotto checklist in Google sheets. I have my own numbers in 6 columns, A2:F5. And I have typed the winning numbers in H2:M2, 6 columns also, with 1000 rows.
Now I want to check if ...
- 57
3
votes
1
answer
107
views
How do I check a set of numbers in different columns against numbers in different columns
I am trying to make a Lotto checklist. I have my own numbers in 6 columns A2:F2, and I have typed the winning numbers in H2:M2, 6 columns also. Now I want to check if my numbers in A2:F2 have any one ...
- 57
1
vote
1
answer
201
views
How to look up if an employee is on time [closed]
I have the raw attendance of my team in one tab, and I created an IF statement to identify whether they are tardy or on time-based on their scheduled time (8 am PST).
N2 = actual log in
O2 = ...
- 11
2
votes
1
answer
92
views
Extracting hashtags from a cell - fixing a 99% working regex formula
I'm trying to use this formula to extract hashtags from a cell. I got it from this very old topic.
=trim(regexreplace(A2, "((^|\s)[^#]\S*)|([^#\w\s]\S*)", ""))
Example source:
&...
- 23
0
votes
1
answer
128
views
How to get total salary from each month from date?
Given that image below, B is date in format [month/day/year] and salary.
From that date, can we total up salary from specific month in yellow table?
For instance, we want the total number of salary ...
- 101
2
votes
1
answer
181
views
Highlighting duplicate emails across multiple Google Sheet worksheets
I'm trying to extend user0's beneficial formula to more than 3 sheets, but Google Sheets reports an error in the Conditional Formatting custom formula field
From this:
=(COUNTIF(Q:Q,Q:Q)>1)+(...
- 23
0
votes
1
answer
352
views
Google Sheets function to create hyperlink based on data in cell, but not if the cell is empty
The solution outlined here is ideal for my needs (converting an article ID in column C into a hyperlink to a CMS that displays in column D).
However, I would like the formula to leave column D blank ...
0
votes
1
answer
57
views
Google Sheets lookup with contains condition
In the monthly statement of my bank account I want to tag every transfer with a category:
Description
Amount
Tag
Emma's veggie shop visa#1022
45
groceries
Zettle payment Bert automobile
345
car
...
- 103
0
votes
2
answers
381
views
Find a cell containing text in a range and return the text in that cell
I have a range of data where each row is in alphabetical order and I want to pull each cell in the row that contains "client" to another column.
- 1
0
votes
0
answers
152
views
Count words or characters in a Notion table view
In a Notion database (table View), I'd like to have the number of words/characters/sentence contained in each note.
Here is an example, where I tediously entered the count manually:
Is there a way to ...
- 140
0
votes
1
answer
3k
views
Displaying Indian Number system (Lakhs and Crores) in Google Sheets
I want a custom formula for Google Sheets to format both crores and lakhs in Indian Number System. I have the custom formula for positive, but couldn't figure out how to add negative number system in ...
0
votes
1
answer
97
views
Repeat a table for each row of another table
I have this table in Microsoft Excel Online:
Col A (Month)
Col B (Date)
January
1/1/2023
January
1/2/2023
January
1/3/2023
And I have another table:
Col C (Dept.)
Col D (KPI)
Dep1
KPI1
Dep1
...
- 25
0
votes
1
answer
38
views
Formula to randomly select the name associated with lowest value from data set & a random from the lowest options if tied with drop-down select filter
I have a data set on Google Sheets where for example,
Name
Scores
Region
State
First
Row
Row
Row
Second
Row
Row
Row
On another tab, I have a drop-down list to select a specific Region and State. I ...
- 19