Questions tagged [formulas]

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

Filter by
Sorted by
Tagged with
-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 ...
TRUDY's user avatar
  • 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 ...
Formula Issue's user avatar
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 &...
iannie's user avatar
  • 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 ....
JM0's user avatar
  • 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
Blues Clues's user avatar
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. ...
Brian Nussbaum's user avatar
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(...
Matt's user avatar
  • 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/...
Elaine's user avatar
  • 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 ...
Grayson's user avatar
  • 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 ...
Mrquestion's user avatar
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 ...
manc's user avatar
  • 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;...
Mateus's user avatar
  • 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") ...
Starnes Student's user avatar
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 ...
Jacob's user avatar
  • 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 ...
Rohit Kumar's user avatar
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 ...
Anto C's user avatar
  • 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 ...
Fir's user avatar
  • 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 ...
Ava O.'s user avatar
  • 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 ...
Clement The's user avatar
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*&...
enm1388's user avatar
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 ...
user avatar
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 (...
Owen O'Neill's user avatar
-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 ...
Trucker Todd's user avatar
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 ...
Juandev's user avatar
  • 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 ...
Salvador C.'s user avatar
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 ...
1.21 gigawatts's user avatar
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 ...
Chris Dreher's user avatar
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 ...
Richarb Krorgzt's user avatar
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,"...
Drewdavid's user avatar
  • 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 ...
Izzy's user avatar
  • 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 ...
user898458's user avatar
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 ...
Jason Patton's user avatar
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) ...
Cyril Duchon-Doris's user avatar
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,...
Chris Dreher's user avatar
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 ...
Clueless Katie Mac's user avatar
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 ...
Axel Frey Guarenas's user avatar
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 ...
Axel Frey Guarenas's user avatar
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 ...
Jun Palmejar's user avatar
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 ...
Jun Palmejar's user avatar
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 = ...
Emma's user avatar
  • 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: &...
Chaoscontrol's user avatar
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 ...
user1134456's user avatar
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)+(...
ŠüR0's user avatar
  • 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 ...
Jack_Luke's user avatar
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 ...
simaglei's user avatar
  • 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.
amh's user avatar
  • 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 ...
Dan Chaltiel's user avatar
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 ...
satyaban sahoo's user avatar
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 ...
Sarah's user avatar
  • 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 ...
Elaine's user avatar
  • 19

1
2
3 4 5
41