# Questions tagged [worksheet-function]

Questions about using in-cell functions/formulas in spreadsheet software.

8,092
questions

0
votes

2
answers

22
views

### Error using countifs function in Excel

I'm trying to use the countifs function to count occurrences of text within a multi-column range when another piece of text appears in a different range.
In the table below, A1:A12 is named colors and ...

0
votes

0
answers

30
views

### Excel returning a non zero value for something that should be zero

I am using this formula:
=IF(Data!F26>=0.875, "aaa", "")
The value in Data!F26 is blank or ""
The cell displays aaa when it should be blank because 0 or blank or &...

0
votes

1
answer

59
views

### Formula for Matching Header to Value

This is a question that I may have difficulty explaining, so bear with me.
On one sheet I have a column which has several cells, some of which are blank, some that have a single value, and some that ...

0
votes

1
answer

15
views

### How can I move a set of columns that are duplicated down under the matching section?

I have an export of CSV data that repeats in the columns. What I need is a clean sheet that has all the columns back together. I made a mockup; the actual data is much more complicated, and there is ...

0
votes

0
answers

5
views

### Counta and Array_constrain formula in Google Spreadsheets [migrated]

I am creating a sheet in google sheets to look like a dashboard from the below data. I am asked to create this with formulas. I am getting the filter part correct. But changing the values from filter ...

0
votes

1
answer

65
views

### Excel formula to seek if the last value falls within a certain period

I have a spreadsheet of data full of blanks and would like a formula that is able to tell me how many and the total value of those values.
For example there are several rows that have values in 202402 ...

0
votes

1
answer

53
views

### Calculating total sales with multiple criterias across two tables

I'm currently working with Table 1 and trying to calculate the total sales [Column C] of products with the same ID and matching stock units and sales units in Table 2. I've attempted to use the SUMIFS ...

0
votes

1
answer

17
views

### How do I find the uppercase letter in the middle of a word, in the middle of a sentence in Excel?

I'm trying to find an Excel formula which will tell me where in a sentence in a cell, does an uppercase letter appear in the middle of a "word". I want to then use that position to pull the ...

1
vote

1
answer

85
views

### Trying to find Date Differences in Excel between multiple enteries

I have a table of Invoices and Date modified. This gives multiple invoice entries with multiple dates by person. What I am trying to do is find the dates between the first and last entry to give me a ...

1
vote

2
answers

102
views

### How do I return the max value from multiple values in a single cell?

I have some cells in my spreadsheet that have multiple dates separated by a semicolon ;.
After some googling, I found that TEXTSPLIT is probably the best formula to break this apart, but it produces ...

0
votes

0
answers

34
views

### Identifying "bad" data combinations in Excel 365

I have a spreadsheet of employee data (time charged to projects and customers) that is tens of thousands of rows long. Each month, the file is updated to include the most recent month data. Each row ...

0
votes

1
answer

23
views

### How can I apply a formula to specify which cells I want to use?

I have a template document where I will want to grab cell values from different spacings depending on another value. Call it myValue which lives in myValueCell.
Say myValue is 1. Then I want to take ...

0
votes

1
answer

58
views

### Replace with wildcards in Excel or batch edit some wrapping formula

I'm only trying to delete some CONCATENATE() function from some formulas in Excel.
What I have; here 'Something' could be a lot of things (digits, <, >, texts, etc…
CONCATENATE("Something = ...

-2
votes

0
answers

33
views

### Can we override F1 key or disable/enable in web excel of microsoft 365 in all browsers, if yes then how? [closed]

I am using web excel where I want to disable and enable F1 function key or override the function of F1 key. And I have been trying to find the solution to the following with no success.

0
votes

0
answers

31
views

### Macro for compressing/expanding formulas in Excel [duplicate]

I have looked for a while but haven't come across something that does what I am looking for. I'm not that proficient in writing macros and if this isn't possible I will find a different solution to my ...

0
votes

0
answers

15
views

### Microsoft Word IF formula

I am having trouble with my IF formula in Microsoft Word, I am trying to automatically output a phrase depending on if cell in my table (J2) is equal to, less than, or greater than 0.
=IF([J2]=0.0,&...

-1
votes

0
answers

17
views

### Is there a way to create multiple rows in a data table based on a single cell entry?

I'm creating a tracking spreadsheet for test samples. Is there a way for a user to enter a single product code that will generate 5-6 rows of for data for the tests required for that product code?
...

0
votes

2
answers

36
views

### Libreoffice transforms formula from range reference to table reference

I have a weird problem
Given the following libreoffice calc sheet:
a defined range "myrange" with the contents:
One
Two
Three
1
2
3
And a formula field:
=myrange[Two]
The result is 2
Now:...

0
votes

1
answer

47
views

### Is there a way to list the names of excel sheets that contain a value?

I have an excel workbook with several sheets. The first sheet is called Master sheet and there I have a table with several names on column B (with a title first "Name").
Some of these names ...

0
votes

1
answer

33
views

### Excel formulas for autofil

I am trying to autofill a cell based on a date selected in a column. Ideally I would like the date selected in the start column to be presented as a filled cell within the correlating column.

0
votes

1
answer

54
views

### Returning value within two column when it match 3 criteria

I would like to return a value 4.42295, if i choose MYR. If I choose SGD will return 1.32963 as picture show below. I had tried some formula in the picture but seem like cannot solve what i need. ...

0
votes

0
answers

53
views

### Highlighting duplicates in Excel with VBA, multiple sheets

I have a workbook set up with a sheet for every month and then a summary sheet, so 13 sheets total. Each sheet, other than my summary page, have two sets of tables set up. A table for each week in ...

-2
votes

1
answer

78
views

### Unraveling a nested IF statement to make it more user friendly

I'm trying to streamline a nested IF statement that my lab uses on a spreadsheet for keeping track of what we make every day for testing - it's a ridiculously complex statement that reads the value of ...

0
votes

1
answer

36
views

### Sum specific cells from a column based on criteria from another sheet

I have three sheets. ScoreInputs, RawScores and ScoreMatrix.
ScoreInputs is basically a single column of raw input scores. Score Inputs
In ScoreMatrix, the first row is a list of score categories, ...

0
votes

0
answers

46
views

### Excel Vlookup Where Column to Search is identified in Preceding column

I am trying to figure out if there is a way to write a formula such that the Table_Array of a vLookup is based on a value from another column. Basically, I want the Vlookup to search on another sheet ...

0
votes

0
answers

38
views

### Searching a large list of strings for a set of strings that may or may not be present

I've looked up similar questions but the answers there don't quite fit my situation, which I think is more simplified.
I have a custom application that can export an .xlsx file listing feature codes ...

1
vote

1
answer

53
views

### Adding formula to all existing formulas in an Excel sheet

I'm setting up a monthly call tracker for sales and customer service. I have almost everything built out, but I'm looking to add the IFERROR function to all cells in the sheet that have a formula in ...

0
votes

1
answer

42
views

### Ordering columns of string alphabetically

I have 3 columns of names that are several thousand rows long.
I need to arrange them in alphabetical order.
I attached a simplified image of the data (the sample table is on the left side of the ...

1
vote

1
answer

37
views

### Show a list where min. number > 5 in worksheet forumula

I'm trying to return a list with a given criteria
My current data looks like
A B
1 Anna 4
2 Brad 6
3 Claire 2
4 Dan 5
4 Evie 2
I want to select ...

0
votes

1
answer

55
views

### Getting IFERROR to move to next row if I paste a value in the previous row

Not sure if the title sums up my problem enough. Basically, I am attempting to streamline my data entry by using formulas. How my data is set up is I have three tabs (Sheet 1, Sheet 2, and Sheet 3). ...

0
votes

0
answers

26
views

### Importing a range of formulas from csv or txt file starts with ' in excel and sheets [duplicate]

I am generating some image URLs with a python script and converting them to a formula. I have tried saving it to both a CSV and a TXT file as others suggested, but both lead to the same problem:
After ...

0
votes

2
answers

39
views

### VLOOKUP displaying wrong information in Excel

new to excel, and trying to do a simple task, where depending on the months in a month range, some percentage will be displayed. However, it's always displaying 0.
Here's a picture to help you ...

0
votes

0
answers

19
views

### INDEX MATCH repeats last valid result

I've searched for this issue and I've found some that were close, but not quite, so forgive me if it has been asked and answered.
When using INDEX/MATCH, all my results are correct, except that the ...

0
votes

1
answer

19
views

### how to use SUMIF to calculate a cell from the figures in 2 other cells based on the date

I have one cell that calculates a figure based on 2 cells, so for example target quantity is in C6 then the completed quantity is in D6 so cell E6 is "=D6-C6" but i only want it to ...

0
votes

2
answers

47
views

### Why are mat1, mat2, mat3, etc. defined and equal to 0?

I'm writing a formula in Excel (Microsoft 365) using the LAMBDA function and happened to name a variable mat1. This resulted in an error and I noticed that the name mat1 was a different colour. I ...

0
votes

0
answers

30
views

### Referencing and evaluating another cell's array formula to create master array using VSTACK, in Excel without VBA/Macros

I'm trying to accomplish this task in Excel without VBA, Macros, or Power Query.
Here is a workbook with example sheets of data, an intermediate sheet, and a sheet showing the desired outcome: https://...

0
votes

1
answer

36
views

### Excel: Referencing a cell from another workbook using a dynamic filepath/filename, inside a formula

I have 50 students, each with a gradebook with many scores I am compiling in a master tracker workbook. The data points come from about 60 different worksheets within each student workbook. Rather ...

0
votes

0
answers

9
views

### Error in Calculated field of google sheets [migrated]

I have my data in the following format:
I added 2 new columns on the right:
(1) Column name: YesInCSAT, formula used: iferror(CSAT% * Total Surveys Given,"")
(2) Column name: AutomatedCount,...

0
votes

2
answers

52
views

### Use a Macro or Formula to replace a single letter with a string of text

This should be pretty simple. I want to use a formula or macro that will automatically replace a letter with a string of text within the same cell. Ex.: If I type the letter "r" in Cell A1 I ...

0
votes

1
answer

41
views

### How to use conditional formatting on a specific formula added?

I want to add a conditional formatting to a cell. In that cell I have a formula that provides 2 dates. The dates in the cell look like this 01/12/2024 and 01/24/2024.
Originally when I had 1 date in ...

0
votes

0
answers

23
views

### I need to compare two excel sheets and then merge the demographics into one of the sheets

I have two sheets:
Sheet AA has 250 Names in column A and a dollar Amount in column C.
Sheet BB has 2000 names in column A and sheet BB has most of the addresses in columns h,I,j,k. Some are missing.
...

0
votes

1
answer

31
views

### If formula not working for hospitality catering manager

I am creating a spreadsheet for my Catering Manager. For example, she can select one of three options for breakfast. All she does is enter the quantity beside the option that the customer has ...

0
votes

1
answer

13
views

### Libreoffice calc: countifs over two rows

I have a table with dates in column A and strings in column B. I'd like the countifs function to count the cells where the weekday in column A is thursday and the corresponding cell in column B is ...

2
votes

2
answers

266
views

### How to nest 2 different formulas in one cell

I'm trying to combine these 2 formulas in one cell. I'm trying to get 2 different dates in one cell. To show in the end (e.g. 2/15/2024 / 2/24/2024.
My 2 formulas
=($C$1-DAY($C$1)+1)+14
=($C$1-DAY($C$...

0
votes

1
answer

53
views

### How could we check if a column of a table is filtered or not, by formulas

I need to check whether a column of a table is filtered or not. The data type of the column is Text. I want to use worksheet Formulas, not VBA macros.
Something like this:
=If(IsFiltered(Table1[Field1]...

1
vote

2
answers

55
views

### SUM select ranges of varying lengths in a single column using null/non-null values in second column to decide SUM indexes in an Excel formula

Example of format for the issue, I need to generate the third row that is a sum of each color block:
I have an odd set of columns in a very large dataset, and I am not great at Excel. I could be way ...

1
vote

1
answer

34
views

### Formulas corresponding to the correct table in my sheet, vba. Multiple tables across multiple sheets

I am setting up a cycle count sheet using VBA. I am having trouble with the formulas in my tables only referring to the first table in the sheet for the input values.
For example, I have 4 tables per ...

1
vote

3
answers

126
views

### Convert an Excel table from one structure to the other

Original table
I have this table in Excel or LibreOffice.
Unit number
Type
Name
1
Object
Top
1
Object
Bottom
1
Object
Left
1
Object
Right
1
Object
Back
1
Object
Front
1
Property
Right-Fixed
1
...

0
votes

1
answer

56
views

### Excel: Using COUNTIF + UNIQUE gives generic error "There's a prblem with this formula"

I'm trying to create an excel function that counts the number of unique entries in a range of (potentially merged) cells. The UNIQUE function returns two values for merged cell, one of which is zero. ...

0
votes

1
answer

29
views

### Looking up a value and putting a different value in a cell in the same row

All the cells I reference can be found in the image below. I'm trying to figure out how to search for a name that I enter in F3 and then put the date I have in E3 into the corresponding cell in column ...