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.

1,826 questions
Filter by
Sorted by
Tagged with
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 ...
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)...
• 15
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 ...
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 ...
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),"") ...
• 199
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 ...
• 199
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 ...
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 ...
• 15
1 vote
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:
• 113
1 vote
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. ...
• 15
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 ...
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 ...
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 ...
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 ...
• 1
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)*('...
28 views

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:...
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 ...
• 3
1 vote
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 ...
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. ...
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.
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/...
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 ...
• 381
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?
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, ...
1 vote
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 ...
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 ...
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 ...
1 vote
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 ...
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 ...
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 ...
• 1
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 ...
• 101
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 ...
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. ...
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 ...
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 "...
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 ...
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 ...
20 views

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 ...
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 ...
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) ...
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 ...
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 ...
1 vote
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 "...
• 13
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?...
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 ...
1 vote
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 ...
• 13
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 ...
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().
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 ...
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*&...