All Questions
Tagged with worksheet-function microsoft-excel-365
71
questions
3
votes
4
answers
5k
views
Excel keyboard shortcut to edit formula in formula bar
When I press F2 in a cell I can edit it inside the cell. However often it's just more convenient to make the edit in the formula bar.
I can switch to formula bar by mouse, just wondering whether there ...
- 21.6k
1
vote
2
answers
53
views
Counting elements based on three criteria located in two columns
I wanted to ask you if there is a way to perform a count by applying formulas directly, without having to resort to the filter option, through the COUNTIF or COUNTIFS function.
From two criteria found ...
1
vote
2
answers
2k
views
Excel: Split a string on a delimiter and determine the length of the resulting array
I have an Excel sheet including columns like this:
| 143 | 423 | | | 423;234 | 134;234;143 | =SOME_FORMULA() |
The data have the following characteristics:
Although numeric, they should be ...
- 705
1
vote
1
answer
378
views
=DATEVALUE() behaving different in Excel 365 online and Excel 365 local
I noticed that =DATEVALUE() behaves different in the online version of Excel 365 (i.e. it does not work) than in the local version of Excel 365 on my PC. Below are two screenshots of the two Excel ...
- 107
1
vote
2
answers
304
views
Excel 365: Array formula based on value above (compounding)
I am trying to perform an operation in a cell based on the value of the cell above it. Doing this in the "normal" Excel way is easy enough. However, I am playing with the array functionality ...
- 281
1
vote
1
answer
921
views
How to return the value of one cell based on the max of multiple ranges of other cells in excel?
I am a bit new to excel and have a spreadsheet where I am trying to use the MAX of a range of cells in order to return the name of the row that that MAX belongs to. So, I have a cell returning the MAX ...
- 19
1
vote
1
answer
611
views
Getting unique items from comma separated lists in Excel
I have the following setup (which is shared by a number of users through office 365 and changed frequently):
I need to collect all unique items from a number of comma-separated lists in a range and ...
- 13
1
vote
1
answer
2k
views
Filter function with IF statement in 1st parameter to perform fill down multiplication OR division
Link to my previous related question for more background if needed: Transpose unique rows to columns while IF statement conditions are met but do not consolidate
My table headers start on row 20 ...
- 45
1
vote
1
answer
532
views
Excel array formula not behaving as expected
I have a little table in EXCEL that uses COUNTIFS to count the number of instances in a dataset that matches four criteria that the user can select from a data validated list in the first four cells ...
- 11
1
vote
1
answer
213
views
Name the first item from a list that is not in another list
I am working with a list of articles in one spreadsheet files as the data source. The data is split into a source reference code in columns A to E, the title of the article in column F and the author ...
- 113
1
vote
1
answer
268
views
autofill a reference of a column to a row in steps
I have the formula =IF(Jan!$L6="w",Jan!$L6,"") in the cell A2.
in the cell right next (B2) to it I'd like to have the formula 3 rows further down so
=IF(Jan!$L9="w",Jan!$L9,"") and then in C2 ...
- 115
1
vote
0
answers
12
views
How can I generate a multi column sorted list in Excel based on data pasted into another sheet?
Okay, so I can't unfortunately go into too much detail due to the sensitive nature of the data, but the short of it is that I've got a table I paste into "Source" that I'd like to have two ...
- 111
1
vote
1
answer
27
views
Unexpected behavior when using named cell in IF condition
I noticed something strange when using named cells in an IF condition in Excel 365 and I would like to know what's going on.
Let's say I have a named cell MyValue, whose value = 15.
Then, the ...
- 111
1
vote
1
answer
36
views
How to list dates material on order will arrive using Excel
I have a table that has all my different SKU's in item number and shows the dates the product should arrive. I want to be able to enter the item number and get a list showing when the product will ...
- 11
1
vote
2
answers
72
views
Please help me stop Excel 365 enterprise from changing my text formatted cells into formulas
I have a spreadsheet where I need to use strings that contain a couple of dash/ minus ( - ) signs in cells, but it will not stop truing to recognize them as formulas! I have already formatted the ...
- 11
1
vote
3
answers
309
views
Microsoft Excel: Use OR Logic and multiple ranges with COUNTIF
I am analysing some form responses and want to count if someone has put a specific answer in a range of different boxes. I have attached an example to explain the logic.
When filling out address ...
- 13
1
vote
1
answer
232
views
formulas in another column that reference spill range not updating when sheet is recalculated
I have a a column that is a spill range that uses the filter and sort_by formulas. This spill range is dynamic and I have an IF statement that references the spill range but upon recalculation of the ...
- 11
1
vote
0
answers
64
views
Excel - Select range depending on cell
I want to select a row range, which I've been doing manually until now. However as data grows, I've tried to select the row according to the relative worksheet, but don't know how to. Here's an ...
- 111
1
vote
1
answer
266
views
Excel : Summation of data that belongs to certain id
It is easier to illustrate the problem based on the following example:
I have two sheets: mapping and data.
The Mapping sheet contains a (one-to-many) mapping table between a country name and the ...
1
vote
0
answers
44
views
Last filled row Function behaves strangely based on column it's in
Function is acting very strange depending on column it's in.
Data start at row 7 (headers in row 6)
Column A has item name B Max Qty. C and D have simple functions and everything afterwards is the ...
- 11
0
votes
3
answers
432
views
Excel - Filter rows based on starting string, read number from filtered rows and perform arithmetic operation to generate column summary
I am having a Project and resource allocation report. I need help in generating the formula to display summary in the bottom section of my report.
Here're the specifications of my report:
Column A ...
- 105
0
votes
2
answers
73
views
Excel formula gives unexpected result
Each year I create a workbook within which there are several worksheets corresponding to each month of the year. I copy and paste the same table with formulas into each new month as the year ...
- 103
0
votes
2
answers
2k
views
Formula to extract only unique values from horizontal range into a single cell, separated by comma
I have a spreadsheet where users are asked to place a value in their allocated column across many rows.
There are a range of other columns used by an Admin area and one of these is supposed to list ...
- 1,289
0
votes
2
answers
129
views
How do I convert a custom function to handle spill arrays (excel)
I need a function that produces a list of sheet names, I already have one that can turn sheet numbers into sheet names but it doesnt handle arrays
The function I would like to convert is
Function ...
- 53
0
votes
2
answers
268
views
Filter columns based on starting string, read number from filtered cells and perform arithmetic operation to generate summary of two rows in Excel
I am having a Project and resource allocation report. I need help in generating the formula to display summary on the right section of the report.
Here're the specifications of my report:
Column A ...
- 105
0
votes
1
answer
38
views
Microsoft Excel - Formula to calculate age for a series of rows with different start and end dates?
I have a spreadsheet of individuals with a year every time contact was made with them. Each individual has a unique alphanumeric identifier. Each individual has a variable number of entries (rows) ...
- 1
0
votes
2
answers
170
views
Can an Excel Filter function base its include on a whole row?
I have a Table from which I pull a list of unique values, then I want to pull all their keys and merge them using textjoin. But the Filter function the criteria to be compared against a single value, ...
- 1,008
0
votes
2
answers
185
views
Calculate when volume counts are cleared based on daily amount received and daily amount processed
I have this data table containing the number of returns per day (Ret Volume) and the number of returns that have been processed per day (Volume Cleared).
What I'm after is the date displayed on the ...
0
votes
1
answer
60
views
is it possible to logical AND two values from two different columns in COUNTIFS?
I have the following formula:
=COUNTIFS(Transactions2023[Result], "Win", Transactions2023[League], "NBA", Transactions2023[Date], "<=" & "4/11/2023")
I ...
- 113
0
votes
1
answer
143
views
INDEX MATCH with multiple matches. Find the MAX match
I ran a GIS operation that provided me with a table to show the 3 closest lengths of pipe to a valve. Main line pipe comes in 12,8,6 & 4 inch sizes and outputs to 4,3 & inch lateral lines.
I ...
0
votes
1
answer
349
views
Sum a range of cells in Excel according to a range (criteria)
cordial greetings
I am trying to obtain the sum of the number of males taking into account an age range that I set previously.
For example, I want to know how many males, between the ages of 3 and 12,...
0
votes
2
answers
1k
views
Excel wildcards not finding the matches they should in VLOOKUP function
I have multiple product families I'm working with, and each family has multiple products within. Some families have more products than others, all product names are unique. On one sheet named "...
- 15
0
votes
1
answer
382
views
Extracting all matching (or close to) values between given START n-th and END n-th values / using Power Query
Consider having two tables. Table1(T1) has Date/Time and Pressure value columns and Table2(T2) has also Date/Time and Info values columns.
T1 is showing pressure change through time, for instance: 21....
- 121
0
votes
1
answer
41
views
Automated Table Of Contents - Adding Details from Last row of related page
Good Day,
Does anyone have recommendations on how to reference a cell with the worksheet name in it and add it to a formula that determines the last row with data in it on that worksheet.
I've used ...
- 672
0
votes
1
answer
160
views
Confusion with data validation (list)
Hello cordial greetings
I am a bit confused, I need to perform a data validation in cell C7, so that the days correspond to the month set in cell C6 and the year found in cell C8. The data is recorded ...
0
votes
1
answer
55
views
Is there an excel formula to calculate all "like" entries from two criteria across two cells?
I'm putting together a personal budget and have added a tab to track daily expenses. I've allocated a "code" against a type of expenditure, and then a "sub-code" for more specific ...
- 1
0
votes
2
answers
7k
views
Cell referencing change with filter
I am quite new to Excel and just figuring out how to do things. I have a question about cell referencing between sheets.
Say I have on Sheet1 this table:
DIMENSION VALUE
X 0
Y 1
Z ...
- 103
0
votes
1
answer
50
views
Look for Date values in embedded in strings
I have a value in a cell that might have a 2-digit month and 2-digit year value embedded in it, and I need a function that would return TRUE if that is found.
Sample input data: sCa!1220LB6qHZbCnsEE ...
- 319
0
votes
1
answer
6k
views
Extract multiple columns with single dynamic array formula
In Excel 365, how can I select non-contiguous columns from a range using a single dynamic array formula? Consider data in columns A:E as follows:
Entering =XLOOKUP(G1,A1:E1,A2:E11) into cell G2 ...
- 133
0
votes
2
answers
1k
views
Excel workday function to skip custom array of holidays
I am trying to use the Excel "workday" function to assist with handling a large list of public and organisational holidays when scheduling education courses in a multi-state country (Australia). ...
- 3
0
votes
0
answers
14
views
Excel - Create 'Next' after spill range
I may be reaching the extent of excel's capabilities without getting into VBA (out of my depth).
I need the "Product Collumn" (Collumn B) to automatically populate (Spill) the subsequent ...
- 1
0
votes
0
answers
19
views
How to return unique, ordered data in Excel?
Last year I asked the following question. The formula has been working great. But I've been asked to provide some additional information that I can't quite figure out how to add.
My input data now ...
- 3
0
votes
1
answer
25
views
Median calculation of IFS function returns #VALUE! error
I need to calculate the median number of construction days for multiple projects across multiple markets and based on whether the project is completed or not.
Examples
ROW A = Markets
ROW B = Days in ...
0
votes
0
answers
51
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 ...
- 1
0
votes
0
answers
20
views
Dynamic Dropdown, MS Excel, Use on Multiple Rows (MS365)
Goal
Using Excel table ("Claims") to track claim statuses, have dropdown values in certain columns (in Columns C, D, F, G) in which the dropdown values for Columns D, F and G are Dynamically ...
- 89
0
votes
0
answers
236
views
How to copy the format of locked cells in Excel?
after the latest update of the Microsoft 365 package to the version 16.0.16227.20202 (32-bit), I am no longer able to copy paste the format from and to locked cells. In the previous version (16.0....
- 103
0
votes
0
answers
287
views
Why doesn't my Excel 365 does not cover the "LET()" worksheet function?
My Excel account information shows me I'm working with Office 365:
However, the LET() worksheet function is not covered, as you can see:
What do I need to do in order to get this function to work? (...
- 2,283
0
votes
1
answer
62
views
IF Function Not Populating Correct Data in Excel 365
I am completely stumped as to why this formula is not working properly. To provide backstory, I am trying to randomize a DMS grid coordinate, for example: 2339**01**N05005**51**W. I only want to ...
0
votes
2
answers
3k
views
How to drag a cell reference formula down a column, but move the cell reference across a row
I am trying to drag a cell reference formula down a column, but have the row value increase as the formula is dragged down.
For instance, in the shot below, cell B3 has the formula "=I3". I ...
0
votes
1
answer
5k
views
Use FILTER function to "Include" match against an Array and not one value if possible
I'm currently trying to filter some data. I have two sheets, one with the users name, e-mail and their function within the company and another with the same e-mail and then which mailboxes they have ...
- 1