Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [formulas]

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

0
votes
2answers
10 views

View only few non-continuous columns

=FILTER(Matrice_data;Matrice_nom=G2) Pretty straight forward. I type a name in G2 and I get all rows with this name. My problem is this one: the "matrice_data" contains many columns, like 30. I want ...
0
votes
1answer
13 views

Splitting multi-line cells into rows

I'd like to be able to split multi-lined cells into rows using only 1 command. I have tried using the command =transpose(split(A2," ")) but when trying to add the 'tab' key as a custom separator ...
0
votes
1answer
41 views

Set formula into a cell based on content of another cell?

Google Sheets: I would like to set a formula into a cell, but this should depend on content of another cell. Example Google Sheet (anybody can edit): https://docs.google.com/spreadsheets/d/...
0
votes
1answer
23 views

How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets

I am trying to remove duplicates from my Google spreadsheet file. Here is an initial table example A B C D HelloWorld1 2 6 41 HelloWorld1 4 3 5 HelloWorld2 6 46 6 ...
0
votes
0answers
10 views

How to highlight a cell in column A if the text in column B matches text in a list on another tab?

I have a Google Sheet set up with place names in column B as below. I have another tab on the same spreadsheet with a list that can change from time to time depending on which stores needs items ...
0
votes
1answer
21 views

Cognito Form Calculation for current week number

I am trying to create a field within a form that will automatically calculate the current week number. It's only for internal use, I just need it to show up on the Entries listing after the form has ...
0
votes
1answer
65 views

How to prevent duplicate entry in google sheets per row

So using the example data below, how can I prevent a duplicate entry with an informative message. The duplicate entry should be based on the row and not the whole sheet. So, if I was to add a second ...
0
votes
2answers
45 views

Conditional formatting if entry is final entry of the day

I have a Google Sheet in which I keep track of transactions, kinda like this: | Date | Funds | Running total | |---------|---------|---------------| | 5/23/18 | 10.00 | 10.00 | | 5/23/...
2
votes
1answer
15 views

Formula for Adding Text to End of Cell (contains URL) - How can I account for cells ending both with and without a “/”?

Working in Google Sheets, I have a column with a list of URLs. I'm trying to add a column that automatically populates the blog link (all sites end in "/blog"). However, my list of URLs are formatted ...
0
votes
2answers
17 views

Formula that will take a number in a cell, and then add that number to itself -1, until itself -1 = 0

I need a formula that will take a number in a cell, and then add that number to itself -1, until itself -1 = 0. For example, if the number were 5, the formula would be: 5 + 4 + 3 + 2 + 1 I could do ...
0
votes
1answer
16 views

Sum a quantity list in an array

I have an array table using ARRAYFORMULA({Fruit Names, Quantity}) that looks like | Apples | 2 | | Oranges | 3 | | Bananas | 5 | | Oranges | -2 | | Bananas | -1 | | Apples | 1 | I'd like to ...
1
vote
1answer
56 views

Conditional Formatting across a range of cells with a formula in Google Sheets

I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018". I have a separate sheet with a data set that has rows that classes end such as "9/24/2018". I want to ...
1
vote
2answers
33 views

Conditional Formatting based on other column

I have a timesheet log with columns DATE | HOURS | DESCRIPTION OF WORK | PROJECT An employee may enter several rows for the same date to log the different amount of time with respect to the ...
0
votes
1answer
43 views

Absolute References

I've been looking for answers to my need, but seem to keep finding answers to the opposite problem. Here is my challenge. I have a google sheet to process payroll. On the first tab is data ...
0
votes
1answer
59 views

Match range to closest of 1 of 3 variables

consider the following sheet ID Hours 1 16 2 24 3 50 4 36 now I need to match the values in Col B to one of 3 variables 16, 25, 30 so what I'm trying to do is match the number in B2:B to the ...
1
vote
2answers
25 views

IFS formula with OR condition inside

Why is the following formula not working? =IFS($B$29=(OR(1;4;10));8310;$B$29=6;9510;$B$29>0;"") When $B$29 is 10, should display 8310, but becomes the empty result.
0
votes
1answer
22 views

Dependant Multi Conditional Formatting

So I am building a spreadsheet and everything was working fine but then I kept tinkering with it and making it more intuitive (and less work on the user) and ran into a snag. What I am trying to do ...
1
vote
3answers
30 views

Pulling data from one sheet to another if

My knowledge is pretty basic when it comes to excel and Google Sheets. I'll explain what I'm trying to do. I'm trying to find a way to filter information from 1 sheet to another in Google Sheets. ...
1
vote
1answer
30 views

Add values between two dates in Google Sheets

So I've already found this question answered several times around here but for some reason, despite essentially copy and pasting the answers it still doesn't work for me. I'm trying to add ...
1
vote
2answers
71 views

Nested IFs and SUMIFs for numbers and text in Google Sheets

I have a very specific thing I want the google sheets to give me, but I'm unsure which approach would be best. Client ID Amount Status Is delivered 56691668 10.00 USD Sent ...
1
vote
1answer
17 views

How to sum max from date range in one cell?

I have a column with dates which looks like this: 24-7-2002 25-4-2004 13-4-2012 6-9-2018 25-4-2004 8-11-2022 etc... I would like to find out how many times the highest date appear and sum them ...
0
votes
1answer
55 views

How can I get ''OHLC'' data rather the Close only?

=GoogleFinance("CURRENCY:EURUSD","all","1/23/2005","50") The code above just fetch the close rate and leaves the other columns blank. How to fix that?
1
vote
1answer
32 views

How to sum up occurrences from range?

I am attempting to calculate how many same duration between two timestamps I have in a given range. The range is Sheet1!A1:B10. I have two columns - first consists of starting time and second ...
0
votes
1answer
39 views

Examine for overlap in Google Sheets?

Let's say I have the following start and end days/times: SHEET1 Resulting in a range of time from Mon 10am through Tues at 11:59pm. I'd like to know whether this range of time overlaps another ...
0
votes
2answers
28 views

Scanning a column and partitioning information to other sheets in Google Sheets

I am a membership manager in an international organization and I was hoping to get some help optimizing our members' contact list. There are three kinds of members, NCs, LCs, and IMs. I have a ...
1
vote
1answer
24 views

Convert cell containing days of the week in first-letter format to day names in Google Sheets

I created a schedule of events in Google Sheets. I have a cell that contains MWF, indicating that the event is every Monday, Wednesday, and Friday. I have a different cell that reports the word ...
0
votes
1answer
41 views

add +1 to year within a text formula

I have a formula which works out my Week Number according to our financial week. =if(ISBLANK(B3), "", if(month(B3) >= 4, TEXT(B3,"YY") & TEXT(WEEKNUM(B3)-13,"0#"), TEXT(B3, "YY") & ...
0
votes
1answer
181 views

Selecting the most recent date, where a name matches?

I'd like to search a spreadsheet for a name, and pick out the most recent date next to it if possible please? My data looks a little like: | Name | Date | |----------------|-----...
1
vote
3answers
105 views

How can I use the capturing group in a function?

=REGEXREPLACE("string", "(ng)", UPPER("$1")) The above returns "string", but should return "striNG". I suspect that this is because UPPER is capitalizing the string "$1" rather than the string with ...
2
votes
1answer
45 views

How to highlight entire row (conditional formatting) using COUNTIFS

For the range C2:Z100 we apply the following formula to find duplicate students' enrollments using conditional formatting: =COUNTIFS($C:$C,C2 , $D:$D,D2)>1 (where column C is the last name and D ...
1
vote
0answers
22 views

How to create a user defined function that maps one value to another in Google Sheets

I would like to create a mapping function that takes as an input 3 arrays of the same length and a double between 0 and 1. Array #1: Each element is the minimum value of the nth bin. Array #2: Each ...
0
votes
0answers
24 views

Convert column with multiple subheadings to sortable table in Google Sheets

How can I convert a column with variable data like this: | <h2>TITLE 1</h2> | | <input ...>Item A</input> | | <input ...>Item B</input> | | &...
2
votes
2answers
30 views

How to sort Google Sheet by number of empty cells per row?

In my sheet, there are 5 columns of data. Some rows are fully filled and some rows have some empty cells. Now I want to sort my sheet from most-filled row to least-filled row. How can I do it?
1
vote
1answer
31 views

Auto-increment a form from a list of words [closed]

I have no skills in this kind of technologies, but I know it can do some awesome stuff. So I hope you will be able to help me. Here is my project: I want to create a form on which I can submit a list ...
1
vote
1answer
77 views

Compound IF Statement in Google Sheet

I'm trying to link 2 Google Sheets with each other & need to create an IF statement. In Sheet 1 I have checkboxes: Yes, No, Maybe. In Sheet 2 I'd like a statement that says basically: If A2=true,...
3
votes
1answer
24 views

Combining Formulas of different types

I'm trying to combine all info from a given number of cells into one cell and to convert all of the information in that one cell to uppercase. Is there a way to make this happen? More specifically, I'...
1
vote
3answers
84 views

Combine text from multiple cells into a single cell on other Sheet?

I need help, I want to combine multiple cells in one other cell in another sheet. I have 4 Collums in my first Sheet1, filed with data: Id's (A1:A) Dates(B1:B) Name(C1:C) Comments(D1:D) And on my ...
1
vote
1answer
327 views

Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?

I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
1
vote
1answer
33 views

AP style poll in Google Sheets

I'm trying to set up an AP Top 25 style poll via Google Sheets ( https://en.wikipedia.org/wiki/AP_Poll ). What I want to do is list the voters' choices on one sheet, and have them totaled and sorted ...
2
votes
1answer
23 views

Adding condition to existing formula to not trigger formula if another cell is blank

I'm new to adding formulas etc. in Google Sheets, and I'm currently trying to set up a library borrowing/returns spreadsheet. I have three columns formatted to dates: 'G' (date borrowed), 'H' (date ...
0
votes
1answer
36 views

Formula for custom number format in Google Sheets (e.g. 5M --> 5,000,000; 50k --> 50,000; etc)

I've been trying to figure this out for a bit but everything I've tried doesn't work. A quick search brought me to the conversion from full number format to condensed format (5,000,000 --> 5M; 50,000 -...
2
votes
1answer
156 views

How do I list Nth matches in Google Spreadsheet?

On MS XLS I can do some aggregate or leech off a structured reference, but I really don't know how to find the Nth result based on word match on Google Sheets. I have huge columns - name, role. I ...
2
votes
1answer
42 views

AVERAGEIFS ERROR “Divide by Zero”

I'm trying to calculate an average # days an account has been open if all 3 specific conditions are met. Average range: R6:R102 Conditions: A6:A102 = "x" S6:S102 = "* EWS *" (contain part of EWS) ...
2
votes
1answer
148 views

Google Sheets Sumif Formula Parse Error

I’m trying to enter a simple =sumif formula and keep getting the dreaded formula parse error or result is 0 Reading other responses, I did try replacing , with ; and still the same error In trying ...
1
vote
2answers
453 views

Multiple IF statements: IF, AND, ISDATE, THEN formula

I'm trying to get a nested formula to work, but I keep getting an error. I tried using the generic formula =IF(AND(A1="this",B1="that"),"x","") but couldn't figure out how to make sure Q6=date. I had ...
0
votes
1answer
17 views

Sum by month and then by month and category formula

Cross-post I've been trying to figure this out for weeks. It's a spreadsheet to help people understand their spending habits. They enter their monthly expenses on one sheet, and the other ...
1
vote
1answer
352 views

Google sheets formula returning “Formula parse error”

Not sure why my formula isn't working It’s super simple but yet returns a formula parse error =If(A19>A22, “no”, “yes”) Value of A19 is 30 and the value of A22 is 0
1
vote
2answers
26 views

Count how many times a certain word comes before and after another in a list

I am trying to evaluate some information from an animal studying exercise using Google Sheets. I'd like to know how many times the animal: forages then allogrooms sleeps then allogrooms etc. Also ...
1
vote
1answer
26 views

Add amount IF statement on Google Sheets

Please see test file at this link. I have the following formula on a cell: =IFERROR(IFS(AND(L5>=60%,L5<=79.99%),J5*132,L5>=80%,J5*150,$L$9>=80%,+2500),0) For example, since L5 is 57% (no ...
1
vote
1answer
27 views

Formula to include “blank” if cell =0

I worked out most of this but cannot get the last part which would require the cell to be showing "0" if another cell is blank. Here is what I tried most recently. This is in Google Sheets, in case it ...