Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

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
1 answer
14 views

Counting different types of text and producing specific text outcome

so I'm trying to create a tracker where I can gauge a pass/warning/fail status for each category. Under "Report Status", I wanted a function that can put out the text "Fail" if one ...
user avatar
0 votes
1 answer
36 views

How to make a formula expand to new rows when new data is added?

I've got a set of data, a column of numbers in this case, that I want to automatically calculate moving averages for when new data is entered. Currently I'm doing this by using =TRIMMEAN($A$2:A2, 0.15)...
user avatar
0 votes
1 answer
20 views

How to create a linked sheet that checks a box for a day if found and inserts text from another column

I'm trying to create a sheet in google sheets for my team that shows me what runners have run on what day of the week. Their activities are automatically imported via Zapier with dates from Strava ...
user avatar
0 votes
1 answer
21 views

Flattening the values (not formulae) of a summary sheet

I work at a company that owns a lot of film gear that they bring on freelance shoots. I'm trying to create a simple, streamlined way for them to notate what's brought on each shoot so that they don't ...
user avatar
3 votes
1 answer
122 views

Formula for matrix (dates in descending order)

As a sequel to this question Formula for matrix (dates in ascending order) how would the formula (=IF($B2="Apple",A2-SORT(FILTER(A:A,B:B="Pear", A:A<$A2),1,FALSE),"") ...
user avatar
  • 199
3 votes
2 answers
117 views

Formula for matrix (dates in ascending order)

Firstly, apologies for this question but I couldn't really wrap my head on how to do this (ultimate aim is to put it in app scripts) Let's say I have a table that is updated daily showing the fruit ...
user avatar
  • 199
0 votes
0 answers
13 views

How to sum a column of variable length, starting at the first cell below the nearest blank cell

Can anybody help with this please? I need to sum a column of figures. The number of rows in the column will be variable, but will be a contiguous range, with no gaps. The range for the sum will start ...
user avatar
0 votes
1 answer
17 views

Google Sheets - Is there a way to filter values but keep them in the respective rows they came from?

I'm looking for a way to filter values based on another column but keep them in the same rows they came from. Here's an example I created manually of the correct output: The goal here is to have a ...
user avatar
1 vote
2 answers
27 views

Google sheet, from 2 lists to flattened table

I have two independent columns with data. How could I use that data to then produce a flattened table like this:
user avatar
  • 113
1 vote
2 answers
29 views

Google sheets function to filter out lowest associated cells

I was wondering if there's a way to use a function (maybe Filter?) in order to sort this data such that column K only contains one of each number, and the highest test associated with it in column L. ...
user avatar
0 votes
0 answers
9 views

MULTIPLE Ifs and concatenate problem

I'm trying to create some "if" scenarios and include concatenate to create unique serial numbers for reports. Example: IF someone chooses "IT" from the dropdown menu in cell I4, I ...
user avatar
-1 votes
0 answers
9 views

TAKE A LOOK AT THESE TWO SPREADSHEETS. IM SO CLOSE. SOMETHING IS WRONG AT THE END

In cell D1 in the PRODUCTS! spreadsheet it is working to get the CC'S amount from the PRICES! Spreadsheet What im trying to accomplish In cell C1 in PRODUCTS! is have a total amount of CC'S. The B ...
user avatar
  • 1
0 votes
1 answer
28 views

Match value of cell if in between range and text match

Desired outcome: I would like to return a value in column C (column D is the values I'd like the formula to return) - the value in column C should return on the criteria it matches the product name in ...
user avatar
0 votes
1 answer
23 views

Google sheets/Excel: Rolling average for the past three months on different sheets [closed]

I was tasked with creating a rolling average column which compares one row to it's exact matches from the last three months (each month has it's own sheet). Currently, my (beginners) work looks like ...
user avatar
  • 1
0 votes
0 answers
20 views

Why does my formula stop working after 1000 rows in Google Sheets?

I am working in Google Sheets and have the following formula: =ArrayFormula(IF(ROWS(A$5:A5)>$C$2,"",INDEX('Annual Schedule'!$A$1:J$1852,SMALL(IF(('Annual Schedule'!$A$1:J$1852>=$A$2)*('...
user avatar
  • 1
0 votes
2 answers
28 views

COUNTIFS in Google Sheets

I have been battling to get the COUNTIFS formula right for the below. I need to count the number of events per day. How many times did the event happen between 2021-04-20 14:24:54 and 2021-04-20 19:16:...
user avatar
0 votes
1 answer
36 views

What formula can I use in order to calculate Z-Score within a specific rolling range?

Doing some sports statistical analysis for fun. Wonder if any of you guys can help me. This is the sheet I'm working with. I'm sure you can see how I'm already experimenting with this in the Z-Score ...
user avatar
  • 3
1 vote
1 answer
17 views

Simplify formula that checks student year level and mark to a letter grade

I am writing a Google Sheet that checks a student's year level, then assigns a letter grade based on their mark in an assessment. If there is no mark, it is designed to leave the cell blank. The ...
user avatar
0 votes
1 answer
16 views

How to make column go reversibly blank with custom validation and conditional format rules

I was able to use scripts editor to make some conditional formatting. Would like the option of covering up the answer column, column B reversibly. Would love some help with a script or how to do this. ...
user avatar
0 votes
1 answer
10 views

Extending a formula (VLOOKUP) to the whole column

I created a VLOOKUP formula inside a Google Sheet, I have 130000 lines and need to extend the formula to the end of the column.
user avatar
  • 1
0 votes
1 answer
22 views

How to add month in Sheets cell that corresponds with formula in below adjacent cell?

I am making a cash flow projection worksheet in Google Sheets. I am using this formula to give me sequential weeks (greatly relieving the tedious entry) =ARRAYFORMULA(JOIN("-",TEXT({"5/...
user avatar
0 votes
1 answer
22 views

Formula to get the data from a source and use it while leaving some defined patterns as spaces

I'm trying to create a formula so that Column E will output the Employee names on Column A. I've provided an image below as my example. As you can see, there is a pattern of 4 vacant after every ...
user avatar
0 votes
1 answer
21 views

Have imported spreadsheet from Excel to Sheets but this formula doesn't work

I have text in three columns and need to know how many times a particular name crops up =SUM(COUNTIF(INDIRECT({"d6:d37","I6:I37","n6:n37"}),"peter")) any ideas?
user avatar
0 votes
2 answers
31 views

How do I automatically record finish position?

I've created a spreadsheet to keep track of the progress that my friends are making in a competition we're doing. I would like google sheets to automatically update which one of us comes in 1st, 2nd, ...
user avatar
1 vote
1 answer
26 views

SWITCH statement in Google Sheets Doesn't Compare Strings Properly?

I have a very simple SWITCH statement in Google Sheets: =switch( D2,"",B2, D2,"A","A", D2,"B","B", "Default" ) If I put ...
user avatar
0 votes
1 answer
29 views

Create a dependent drop-down menu for an entire column

The aim: For my work, I would like to create a google spreadsheet where we can document our errors in processing the order transactions. For this I need two drop-down menus; the first shows the ...
user avatar
0 votes
2 answers
28 views

Google Sheets: Sum till particular date

I have time-series data that follows no predictable entries. For example as below. Date Units 01 Jan 2022 1 05 Jan 2022 2 08 Jan 2022 1 30 Jan 2022 1 04 Feb 2022 1 10 Feb 2022 1 13 Feb 2022 2 ...
user avatar
1 vote
1 answer
14 views

SUM Function Works Fine in One Sub-Sheet(?) but Not in the Other?

I have a SUM function that works fine in one sub-sheet(the tabs default titled sheet1, sheet2, etc. on the bottom of the screen) but decides not to work in the other. This is what the function looks ...
user avatar
0 votes
0 answers
41 views

Help with SUMIFS formula with horizontal and vertical criteria + REGEXMATCH

I need some help trying to sum totals for different events based on horizontal and vertical criteria. I've tried doing different combinations of SUMIFS + INDEX + MATCH formulas, including transposing ...
user avatar
0 votes
1 answer
22 views

"Multiply" (Concatenate) Text Columns in Google Sheets

Simple question that I can't seem to find a solution for. I have text data in two columns like Name Num a 1 b 2 and I would like to produce a single formula if possible to output a 3rd column ...
user avatar
  • 1
0 votes
1 answer
27 views

Google Sheets - countif with dynamic range

In sheet1 have a column with a list of IDs, each with a set of statuses: ID Apr May Jun 1 Load Load Complete 2 Load Complete NA 3 Load Load Load In a separate sheet, sheet2 I ...
user avatar
  • 101
0 votes
0 answers
5 views

I need help how to calculate estimated hours spent per article by the total number of working hours and total numbers of articles completed

This is what my sheet looks like. I need to get the average mins spent by the editor per article. I have the total number of working hours and the total number of articles completed. I hope someone ...
user avatar
0 votes
1 answer
19 views

How can i make a formula to determine payment types

I am having trouble nesting and using multiple criteria. I want a column to determine if payment type was CASH, CHECK or WAITING depending on if the cells for Check# and Payment are containing data. ...
user avatar
0 votes
0 answers
13 views

How to trace cells from a formula when the formula uses cells form a different sheet?

I have a complicated budget spreadsheet which I update regularly. I have one sheet which is essentially just my bank ledger where I categorize all transactions & purchases. And then I have another ...
user avatar
  • 1
0 votes
1 answer
27 views

How do I find the appearance rate of one list of cells in another list of cells?

I imported all of the data from my iTunes library into a Google sheet for my own amusement, and I want to know by album how many songs from each album I thought were good enough to put in my "...
user avatar
0 votes
0 answers
11 views

Cognito Dynamic Choices/Lookup

I have a form where on page 2 there is 28 items that the submitter has 3 choices for for example Choice 1 is good, 2 is bad, 3 is NA. On page 3 the submitter then has to list each item they selected ...
user avatar
0 votes
0 answers
11 views

Array formula giving error of different sizes

I am trying to create a formula to count based on two different set of or conditions. If I remove the second array ({BB$3,BB$4,BB$5}), then the formula works but when I add the second condition it ...
user avatar
0 votes
1 answer
20 views

Tournament table Google Sheets

I want to make a tournament table and I need to: make A1 cell type amount of points based on score (B1 is player 1 score, C1 is player 2 score) I want it to automatically detect wins, draws and loses ...
user avatar
0 votes
1 answer
55 views

Google Sheets - efficiently aggregating one column for grouping, based on minimum value in another column?

I have a sheet with upward of 1000 rows with the following schema: GroupCol | NameCol | NumberCol Example spreadsheet (much smaller data) In the dataset, any given value in GroupCol is repeated 1-10 ...
user avatar
0 votes
1 answer
21 views

How do I make text in a column appear after the rows have been filled?

I have made this spreadsheet to help our mechanical workshop track labour. I have a problem on my spreadsheet where column K displays "follow up". The end users (technicians and foreman) ...
user avatar
  • 3
0 votes
0 answers
18 views

Calculating total number of VISIBLE checked (=TRUE) boxes in a range

I have a range of cells containing checkboxes that are manually ticked based on criteria met and applied to a total value calculation (TVC). The current formula =countifs(C16:16,TRUE) worked for what ...
user avatar
  • 1
0 votes
1 answer
14 views

I Need Help with Formula

Cell A1 displays the current date, A2 is a date 6 months in the future. I need a formula for A3 to display A2 when A1 is 3 months away. I'm new to making any kind of formulas, I have copied and pasted ...
user avatar
  • 1
1 vote
1 answer
34 views

sumif not working sometimes

I have read similar questions but unfortunately didn't help me. Here is a copy (with sensitive info redacted) of my data. Google Sheet In column U and V I am trying to sum columns D to O if T is "...
user avatar
  • 13
0 votes
0 answers
12 views

Can I make the reference cell variable dependent on a formula specifically for the row? [duplicate]

I want a cell to populate with a reference dependent on the value of another cell. For example, I want to specify column D, but I want the row reference to be the value given in cell B3. Can I do that?...
user avatar
  • 1
0 votes
0 answers
36 views

Average value by highest sums

I'm having trouble with writing the logic for the formula The results should be as above. Markdown table Closing TEU Average per day 23/3/2022 62 no 120 24/3/2022 49 no 120 25/3/2022 168 48 120 ...
user avatar
1 vote
1 answer
27 views

highlighting a single cell when partial text shows up in a range of other cells

I work for a physician office and we keep track of which patients are ready in a room and which doctor goes in to see that patient. We use a google spreadsheet to track this information. I want to ...
user avatar
  • 13
0 votes
1 answer
21 views

Conditional formatting in Google sheets to indicate overages

To help identify issues faster I am trying to add conditional formatting for column Z to turn red if the amount in that column is greater than the amount in the columns AA or AB and only if column AA ...
user avatar
  • 3
-1 votes
1 answer
47 views

Count all cells with specific data between certain dates

I have a schedule of a team planned on multiple projects on certain dates. I'm looking for a formula that retrieves the amount of days (cells) on PROJECT-A until TODAY().
user avatar
0 votes
1 answer
58 views

ArrayFormula COUNTUNIQUE

How would I turn this formula into an array formula so it automatically populated when a new row is created? =countunique(Z23:AC23) I need the formula to count the unique properties in a row between ...
user avatar
  • 1
0 votes
0 answers
34 views

Referencing text in a specific cell in =SUMIFS formula

Very new to Sheets so forgive any non-clear terminology. How can I use text from one cell, as a criteria value in a =SUMIFS formula? i.e. =SUMIFS('2021 Data'!D:D,'2021 Data'!B:B,"ABC Florist*&...
user avatar

1
2 3 4 5
37