Questions tagged [google-spreadsheets]
Google Spreadsheets is a part of the Google Docs web app suite. Questions about Google Spreadsheets are off topic and should be asked on webapps.stackexchange.com, unless they specifically involve your computer or browser.
472
questions
0
votes
1
answer
40
views
Count just columns consecutive in sheets
I am trying to add or count only the values that are consecutive between the columns, that is, I want to be able to identify by name when the condition that there is six times the number one is met, ...
- 1
0
votes
1
answer
367
views
Count rows matching 'x' in column A OR column B, while ALSO matching 'y' in column C
Sample Data:
A B C
1 Jene Alex
2 Sasha Jene X
3 Alex Sasha X
4 Alex Jene
5 Alex Sasha X
I want my count to include all rows that have the name in either column A or column ...
- 291
0
votes
1
answer
156
views
How to add a comma and the word "and", "then" ,"or", and so on to a list in Excel or Google Sheet
=SUBSTITUTE(TEXTJOIN(", ",TRUE,$G3,$I3,$K3),", "," and ",MAX(1,COUNTA($G3,$I3,$K3)-1))
Right now the return is: G3, I3 and K3 (the last comma before "and" is ...
- 1
0
votes
1
answer
24
views
Need help in Excel Adding 2 columns
i just want to add column 2 to column 1 if and only if column 2 has just 1 text character in its cell .enter image description here
- 1
0
votes
1
answer
2k
views
Update reference in Google Sheets formula when a form adds new rows
Using help from various sources and bits of a formula taking data from different Google Sheets columns, I now have the following formula for calculating some numerical totals. I cant get it to update ...
- 1
1
vote
0
answers
119
views
Copy + Paste a cell, keeping the cell reference the same but increasing the column increment by one
I'm working on a dashboard (Google Sheets) at work, which shows all the figures for 12 different sites. The sites are arranged in rows with columns used for different figures (revenue, bar sales, ...
0
votes
1
answer
52
views
How to FIlter/Match spreadsheet data based on two row headers?
I need to find a value based on a specific column and header, that also fall within another merged header (spans 3 cells) like the example below:
|January 2020 |February 2020 |...
- 259
0
votes
0
answers
217
views
How to VLOOKUP based on two column criteria?
I'm not exactly sure if this calls for a vlookup, but it's the closest I could think of to solve the following scenario:
Animal|Color|Total
------------------
Cat |Red |150
Cat |Blue |85
Cat |...
- 259
0
votes
1
answer
149
views
How can you format each row from a pivot table to display transposed with the headers repeating themselves?
From a pivot table, I am attempting to take each row of data and format it transposed with the headers from the pivot table present in each instance. Here is a link to a Google Sheet that has a sample ...
0
votes
0
answers
46
views
Reading a section of data from one sheet and applying conditional formatting to equivalent entries on another sheet
I have a one sheet of data sorted by date, where A2:X45 are one year, then below A47:X92 are the next year, etc. Months are by column from A3:X3. A2 and A46 are where the year is labeled. A1 is a ...
0
votes
1
answer
396
views
How to select some data labels to be shown in Scatter Plot Chart (Google Data Studio)
I've a massive amount of rows, therefore I have lots and lots of data labels:
Huge amount of labels in Scatter Plot
How select some points to show labels and hide others?
(Or if it's not possible, is ...
- 1
0
votes
0
answers
155
views
How to bring sum of previous column in to next column automatically, in Excel or Google Sheets?
I'm new to this. I'm trying to make a personal budget sheet, where I can bring the previous months' sum into the next month's column as "savings from last month". Basically I want to show ...
- 195
0
votes
1
answer
1k
views
Counting items from a drop down list
I’m trying to make an exercise spreadsheet to count sets for a certain muscle group.
I organized movements into muscle groups.
Then, I wanted an user-facing input section like this, where the user ...
- 1
0
votes
2
answers
58
views
Multiple worded cell contents put into one cell
I am making a pricing sheet and I currently have this formula
=IF(B1>0,A1,"")
With this formula the contents of A1 might say something like "Apple" and B1 is the quantity, so as ...
0
votes
1
answer
332
views
Google Spreadsheet - How to repeat a function for every row inside 1 formula
[
So I want to make a formula that outputs if a vehicle meets the restriction or not. And I have been using this function in the formula:
IF(ISBLANK($A$7),,REGEXMATCH($A10,$A$7))
Is there a way for me ...
- 1
0
votes
1
answer
114
views
How to auto-recalculate column cells if values change in preceding column?
Backstory: I built a budget spreadsheet for my personal finances. I calculated my obligations for the month, and the leftovers I want to divy up into specific savings account buckets on a monthly ...
- 5
5
votes
2
answers
8k
views
Can I configure MacOS to open CSV and Excel files in Google Sheets?
Is there a way to have MacOS open CSV and Excel files in Google Sheets when you double-click them? I've tried associating the Google Sheets shortcut in Applications but that doesn't work.
- 996
1
vote
0
answers
63
views
Formula - Get previous date and sum of unique months based on unique user
Goal Estimate the next date Abbie will make the purchase based on average days.
Screenshot to give a better idea:
Questions
A.1 & A.2 - how to search for the most recent previous purchase date ...
- 11
0
votes
2
answers
10k
views
How to make a pivot table that counts from multiple columns?
I have a workday schedule in which I will count how many times I do each activity across the week. Here's an example:
The pivot table only allows me to count from MWF or TT but not from both without ...
- 101
0
votes
2
answers
2k
views
How do I subtract some values based on the text value of a different column?
I have a spreadsheet that is tracking payments between 2 people. It looks like the following:
I'm looking to subtract the value of the Amount column from the retrospective persons Left to pay amount, ...
- 38
0
votes
1
answer
40
views
Looking for assistance with auto populating data from a ongoing list
I am attempting to copy the data from a list columns A&B as names and photos and would like it to auto populate horizontally into rows below but only to the 9th column as shown then continue in ...
- 3
1
vote
1
answer
200
views
Searching a value of partial text from another sheet
My scenario is, I have 2 worksheet:
Sheet1 contains the name of the restaurants, and Sheet2 contains the name of restaurants to avoid.
My issue is, I use a formula in conditional formatting that only ...
- 11
0
votes
1
answer
121
views
Is there a way to return certain rows on Google Sheets that I have tagged?
I have a large list of books that I would like to tag and be able to filter out the row/book I am searching for without having to scroll through the entire sheet.
For example:
Book A humor, non-...
2
votes
1
answer
1k
views
CONCATENATE cells in a column IF condition in other colum is met
Using Google sheets (or Excel if Google sheets can't do it). I'm trying to concatenate any cell in B column, if its corresponding A cell is "include". The columns are very long and therefore ...
- 25
0
votes
1
answer
42
views
Insert big text into multiply rows by keyword into Excel or google spreadsheets
If I have multiline text like
Line:
x1 = 10, y1 = 100
x2 = 30, y2 = 40
Line:
x1 = 100, y1 = 120
x2 = 130, y2 = 140
And so on. Is it possible to insert it at once at Excel or google spreadsheets so ...
- 103
1
vote
1
answer
45
views
Excel/Sheets: Average only the input data in a range of data?
I've got an annual budgeting spreadsheet in which each month is listed along with given categories of items. I'm trying to find my average monthly expenditure on any given category, but don't know the ...
0
votes
1
answer
65
views
How do I access an open API like Ethereum with a spreadsheet like excel or sheets?
Is there a way to access the Ethereum API using either excel or sheets or some other database with a website front end? I'm trying to track my transactions.
- 171
1
vote
1
answer
1k
views
SUMIF only for last condition row
I have a spreadsheet where I need to total values using a SUMIF function and record that total in another column, but I only want it to record that total for the last instance of a value in the column....
- 224
0
votes
0
answers
22
views
Listing function points in a spreadsheet/excel [duplicate]
I am working on a Pre-Calculus Polar coordinate graphing project that incorporates some creativity; My current plan has me graphing the Nautilus Shell as my focal point.
However, I have to list points ...
0
votes
0
answers
291
views
How to get a vertical bar chart with the sum of rows grouped by month
I have a spreadsheet with over 300 rows, and in each row I have a Created At column with this date format: 20/Apr/21 5:34PM.
I would like to have a vertical chart that is the sum of the rows for each ...
- 173
0
votes
2
answers
55
views
Google Sheets / Excel - Create a minimum list based on multiple cells
I'm trying to get a minimum number (C) if A B and D combined match with any other row
A
B
C
D
1
iOS
Safari
14.0
mobile
2
Android
Samsung Internet
13.2
mobile
4
Macintosh
Safari
14.0
desktop
5
...
- 101
1
vote
0
answers
33
views
Optimizing sales in excel/google sheets
I have a table that looks like that:
investment
p1
p2
p3
1000
100
50
80
2000
200
60
95
3000
300
70
110
4000
400
80
125
5000
500
90
140
6000
600
100
155
7000
700
110
170
8000
800
120
185
...
- 111
0
votes
1
answer
19
views
Using IF to apply different rounding rules based on value range - error with too many IF arguments
I am trying to build a formula that will round a value in a cell based on these requirements
round to 0 if less than 2%
round to the nearest 2% increment up to and including 10%
round to nearest 5% ...
- 1
0
votes
0
answers
741
views
How to apply Text cell format permanently on a google sheet's column?
I recently added IDs as Number in a column those are basically built as alphanumeric.
Then I've applied Text cell format on those IDs to keep their data consistent with the other cells in that column, ...
- 21
1
vote
1
answer
389
views
How to copy rows of specific columns based on a criteria (and then continuously update)?
I have 2 sheets, let's call them sheet 1 and sheet 2.
In sheet 1, I have some data where essentially at its core its a Scoring tally, something like this:
Name
Age
Gender
Score
Jack
12
Male
155
...
1
vote
1
answer
68
views
Get a drop-down list from another sheet based on lookup
I have a list of CA in my Source sheet that looks like this:
And in my main sheet I need to get a dropdown list of "little a" for each CA, like this:
Main sheet:
Sheets:
For example: ...
- 13
1
vote
1
answer
266
views
How to calculate max sum held over a month?
I need help with a formula that needs to calculate the max sum held at one time for each month.
In this example it would be 54 for March (12+13+14+15, held on the 29/03/2021). Month is determined by &...
- 23
0
votes
1
answer
1k
views
Can't use some Alt+ keyboard shortcuts in Google Sheets like I did in Excel on some computers
In Excel there are some Alt shortcuts like:
Alt+E+S+V: paste special value only
Alt+I+R: insert a new row
In Google Sheets, they can also be used. When I press Alt+I for example, I see this popup at ...
- 267
4
votes
1
answer
3k
views
Conditional borders in Google SpreadSheet
I would like to have yellow borders on cells that are column's maximum as follows:
That is, I need to have conditional borders. I understood I should use Google Scripting but I have no idea nor ...
- 181
1
vote
1
answer
76
views
Sum and lookup over range in spreadsheet
I have a google spreadsheet that looks like this
Items
Item
Value
A
10
B
12
C
15
D
20
Calc
Total
Items
A
A
B
A
A
C
C
C
In Total I want to have the a formula that sums the value of the ...
- 113
1
vote
1
answer
375
views
Extracting text between two repeated sets of values in Excel
I have campaign names that conform to the following structure:
[Network] - [Language] - [Country] - [Channel] - [Campaign Type] - [Free Text]
This could look like:
Google - English - India - Display -...
- 11
0
votes
2
answers
106
views
How do I sum the nth number from the right in all cells
I am using a spreadsheet to collect all my questions and answers for a quiz I am doing. In the answers column, every answer is ended with [#p], # representing the numbers 1–9, never exceeding 9. ...
- 159
0
votes
1
answer
41
views
How to reference "This" Column?
Maybe I'm dumb, but how do you just reference "This" or "Current" column? For example, counting entries in a column.
I know you can enter a formula & drag it, or copy it & ...
1
vote
0
answers
89
views
How to show different type of legend below specific columns under X axis on stacked bar chart
I have drawn a stacked column chart in Google sheet. I want to put different legends below specific columns as secondary label under the x-axis; here is my spradesheet link: https://docs.google.com/...
- 11
0
votes
1
answer
93
views
Merge two seperate columns in Google Sheets into a single column [duplicate]
I have a list that contains people's names in one column and their company's names in a second column. At the top of each column there is a label, i.e "Name" for column A, "Company"...
- 3
0
votes
1
answer
116
views
How can you show the first cell in a cluster in Excel? And how can you lookup the value?
Several questions. But let me break down the problem of what I am trying to do:
I have a condition to show YES/NO if a certain value is met. If YES >= 60 and NO <=30 - this is easy I can do ...
- 1
1
vote
2
answers
208
views
Is it possible to reuse the same sumif range?
In the following table, I constantly reevaluate a2:a4 to run different calculations against it. In a real table it would have been thousands of rows like it.
Is there a way to somehow store a2:a4 in a ...
- 946
0
votes
1
answer
239
views
How to highlight down columns for many columns at once that are separated in an excel sheet?
I have 30 columns of data with over 10,000 rows. I'm using Ctrl+Click to highlight specific cells and then use Ctrl+Shift+Down arrow to highlight to the bottom, but I noticed it won't let me do that ...
- 1
0
votes
3
answers
144
views
Sum of vlookups for each value in a row
There should be a way to do this, and yet I'm struggling...
==The Data==
Sheet1
A B
+---------------+-------+
| Fishy_Product | Price |
+---------------+-------+
| Cod | $9....
- 3
1
vote
1
answer
94
views
Microsoft Excel/Google Sheets - Update Adherence
I would like to produce a result wherein it checks if an update is done within 3 hours, it would be pass. If it goes beyond 3 hours, then it would be a fail. So this is how my raw data looks like:
...
