## New answers tagged worksheet-function

0
votes

### Calculate monthly profit/loss from stock options positions

To calculate the monthly income earned from specific options strategies in Excel, you can use a combination of functions like SUMIFS, MONTH, and YEAR. Here's how you can set up the formula:
Create a ...

0
votes

### Excel - result of =filter() in merged cells

Currently, I suggest you left the filter results (From A3 to A5), then try following formula in other column with no unmerged cells to check if you merge cells, will the result be correct.
=INDEX($A$3:...

0
votes

Accepted

### Subtracting from today only if other cell is empty

Not sure what you're trying to do with the OR there... which is why it doesn't work.
You need a simple IF like this:
=IF(F11="","",IF(G11="",TODAY()-F11,G11-F11))
IF ...

1
vote

### How can I do multiple IF conditions in MS EXCEL?

This is called a nested IF function. What you want to do is to at each step specify what to do if_true, and then if_false put another IF with the next condition.
Something like:
=IF(A1>75000,3000,...

-1
votes

### How to use AND and VLOOKUP functions to assess multiple items without getting N/A or VALUE errors

=IF(A2="Secondary",IFERROR(IF(EXACT(VLOOKUP(B2,$G$3:$G$22,1,FALSE),B2),"Information"),"N/A"),"N/A")

2
votes

Accepted

### Excel - return True/False value by searching multiple keywords in one column

I think the simplest way to do it would be to to add an OR to the first logic of your AND.
Like this:
=IF(AND(OR(D9="Matched",D9="Complete"),E9="Yes"),"Ready",&...

1
vote

Accepted

### How to use AND and VLOOKUP functions to assess multiple items without getting N/A or VALUE errors

Your issue with the #N/A in C5, comes from the =VLOOKUP(B5,$G$3:$G$22,1,FALSE) (the 2nd part of the first AND) results in a #N/A throwing off the whole following function to stop working.
And the #...

0
votes

### How to turn on and off data in a formula in excel

I came up with a solution on my own. I added an additional column to read 0 or 1. 1 if the inventory is needed 0 if not. Then in my space requirement column I added an IF statement (IF(A1=1,(calulate ...

1
vote

Accepted

### Cell Reference based on Row

You need to do a SUMIF on the Used range, and subtract that from the Qty.
In the first Remaining cell (the one in the ORD1 row) you can enter (and drag the fill handle down to all the orders):
=C2-...

0
votes

### Trying to Sum data within a selected time period (in decimal years data)

You can use:
=SUM( ( "BeginningDate:LastDate" >= "First date" ) * ( "BeginningDate:LastDate" <= "End date" ) * ( "Cum. Fire column" ) )
It ...

0
votes

### Message Formatting with Excel

No, what you want is not directly possible with Excel. It simply does not do two things: 1) Support Java message format/ting as a formal, programmed in thing, and 2) Allow "inline" ...

1
vote

### Why are my columns disappearing after I refresh the data?

You ARE in fact "losing" column S.
True, I'm sure, not part of the original query, but clearly now the query does actually produce a column of data that is placed overtop the current ...

0
votes

Accepted

### How to dynamically return in each row the number of occurences of a string found in every column?

I'd like to share what I managed to come out with, which has to involve multiple formulas:
In A2, I manually define the starting cell of the searched area.
In B2, I automatically calculate the ...

0
votes

### How to highlight all cells that has been sum up

Instead of conditional fortmatting, you could format the data as table without headers and add a totals row.
You could then color the totals row field to your liking and if you add more items to the ...

0
votes

### How to highlight all cells that has been sum up

This will happen automatically if you click on A4 and then click inside
the formula :

0
votes

### Looking at Sales Data. How to determine if an order was a new order or a reorder for a specific customer and product?

You can use the following formula:
=IF( SUM( ($B$2:$B9=A10) * ($D$2:$D9=D10) * ($A$2:$A9>($A10-183)) ) = 0, "New", "Reorder" )
It uses the old school technique of simple "...

0
votes

Accepted

### Copy cells next to each other to another work sheet

This is a classic situation where you can use a simple VLOOKUP().
I assume your name/number data is stored in the range C1:D21 on the sheet "CONTACT LIST" and in cell G1 on sheet "...

0
votes

### COUNTIF using two conditions while ignoring duplicates

You can use COUNTIFS for this, as follows.
First create the column headers with the types and first row with the years (something like below, I put the type in the columns and the years in the rows ...

0
votes

### COUNTIF using two conditions while ignoring duplicates

Under the assumption that you simply want to count everything for the different vehicle types, an easy solution would be the following. I stored the three columns of the data in the range A1:C22. The ...

1
vote

### Excel Function to Concatenate & Exclude Zeros

You can extend the following formula to include as many items as you wish:
As per your tags, this assumes no Excel version constraints
=TEXTJOIN(
",",
TRUE,
BYCOL(
FILTER(...

2
votes

Accepted

### Obtain leftmost cell and rightmost cell in a range that do not contain an error

With Office 365 we can use TAKE() and TOROW() to return the correct numbers.
TOROW() has the ability to ignore the errors and TAKE can take the first and last of what TOROW() returns:
=TAKE(TOROW(C3:...

1
vote

### How to paste only the formatted value not the actual value

Copy the cells, paste them in Word. Copy back from Word to Excel.
Just thought of this after a few minutes of Googling / not finding any solution...

1
vote

### Equation for excel cell please

Assuming the idea here is to figuratively move the cursor from the left of the input n steps to the right (steps == 1st digit of your instruction), then n steps to the left (steps == 2nd digit of your ...

1
vote

### Excel Function to Concatenate & Exclude Zeros

Formula
Only concatenate "Dog:"&B4 with "Cat:"&C4 , if B4 is not 0 * see note
=IF(B4<>0,"Dog:"&B4,"")&"Cat:"&C4
* ...

0
votes

### Excel Function to Concatenate & Exclude Zeros

You want to use a conditional if statement in this concat string.
Syntax is:
IF(condition,value when true,value when false)
For what you are doing, if the value for "Cat" is in "C4&...

1
vote

### How do I sum the lowest two values of two different column or range?

The difficulty with the first part is that you are finding two permanent 0's with SMALL (1,2) and finally one value of some kind (3).
The reason is that your values begin in D27 but the SMALL function ...

0
votes

### Nested if conditions with Vlookup producing N/A instead of next conditional statement

Yes, it can be used within your construct.
Basically, your AND produces not either TRUE or FALSE, but rather TRUE or #ERROR!. That comes from your VLOOKUP's either producing a "result" or an ...

0
votes

### Excel formula for calculating average of 52 cells out of possible 104 where blank cells exist

You can use the following formula to get the average of the last 52 non-blank cells in a range (the data is in Column A):
=IFERROR(AVERAGE(IF(ROW(A:A)>=LARGE(IF(A:A<>"",ROW(A:A)),...

0
votes

### Nested if conditions with Vlookup producing N/A instead of next conditional statement

Use IFERROR(VLOOKUP(first set of args),VLOOKUP(second set of args)).
IFERROR() provides the value of the first argument, unless it is an error, in which case it provides the value of the second.

0
votes

### General formula for multiplying numbers by themselves continuously, and divisibility of a column by a particular number

Enter 2 in e.g. A1, then =2*A1 in A2,
select cell A2 and use the "fill down" function to populate cells below A2.
Or more in the style you're suggesting =$A$1*A1 in A2, to multiply with A1-...

0
votes

### Excel function determine if a cell is visible

It's an old task but the issue still appears today: how to detect hidden rows, especially in a bit more complicated situation.
Searching on the web shows a SUMPRODUCT construct, which is very slow in ...

0
votes

Accepted

### Automatically cross-reference a word in a call

We're missing context (of the rest of the sheet and where this formula is going to be) but in principle it seems that what your asking is simply to have the cell reference instead of the word.
So ...

0
votes

Accepted

### Auto insert words from one cell to another

You can use the UNIQUE function to get a list of company names from columns C, one per company name. But given that your column C is interrupted with the names of the day and the headers, you'll also ...

0
votes

### how to filter "all rows where column x doesn't contain [0-9]"

Extremely similar to @Raysafarian's answer, but here is an alternative option just in case that is useful to anyone:
=COUNT(SEARCH({1,2,3,4,5,6,7,8,9,0}, A1))>0
It doesn't matter with numbers ...

0
votes

### Formula to Combine 2 Different Logic Arguments

What you need is to have an OR and inside it the two logical scenarios (the first one with an AND.)
Your formula =IF(AND(A1=AUSTRALIA, B1=RED, B1=YELLOW, B1=BLUE),"YES", "NO") ...

0
votes

### Is it possible to hide #Value in a long formula? I tried IFERROR but excel said there's too many arguments

The “You’ve entered too many arguments…” doesn't have to do with how long the formula is, it simply means you have put it in the wrong place (probably inside the IF but without nesting it.)
The ...

-1
votes

### Is it possible to hide #Value in a long formula? I tried IFERROR but excel said there's too many arguments

If you want to hide #VALUE! in a lengthy formula, you can use the IFERROR function, but it may not work if there are too many arguments. One solution is to split the formula into smaller parts and ...

-1
votes

### Find and sum all numbers in a cell

If you are working with higher version of Excel like, XL-2021 or 2022 or office 365 then you may try this,
=SUM(FILTERXML("<root><data>"&SUBSTITUTE(A1," ","<...

2
votes

### Find and sum all numbers in a cell

One option:
=IF(A2="","",SUM(0+TEXTSPLIT(A2,TEXTSPLIT(A2,SEQUENCE(10,,0),,1),,1)))

0
votes

### Excel / Sheets: How do I filter addresses based on a list of zip codes?

Basic Syntax
=FILTER(range,COUNTIF(lookupValues,zipCodeColumn))
Sample Formula
=FILTER(A:D,COUNTIF(G2:G5,D:D))
More Excel Reading
FILTER
COUNTIF

2
votes

Accepted

### Microsoft Excel - Global formula to arrange multiple pattern text as desired

One option:
Formula in B1:
=LET(x,{"All De ","All Des ","Che Du ","Esp ","Imp De La ","R De La ","R De L'","R De ",&...

0
votes

### Finding the first Non-Numeric Character in a String

Just out of interest I'll drop an answer to an (nearly 10 years) old question. I came across this while browsing and thought I'd add in an alternative based on new dynamic array functionality within ...

5
votes

Accepted

### Finding the first Non-Numeric Character in a String but not excluding decimal denominators

I have four approaches:
Uses the CODE or CHAR functions
Uses the SUBSTITUTE function
Uses a recursive LAMBDA function
Appends & prepends digits
1. CODE / CHAR Approach
TRUE finds the ...

1
vote

### is it possible to logical AND two values from two different columns in COUNTIFS?

With the latest version of your formula, the issue is at Transactions2023[Date] + Transactions2023[Time], DATEVALUE(Transactions2023[Date]) + TIMEVALUE(Transactions2023[Time]) <= 45027 + (17/24)
...

1
vote

Accepted

### Use COUNTIFS count values for specific range of the results

I don't think it's easily done, but here is one of doing it using an array formula:
=COUNTIFS(OFFSET(A$1,0,0,SMALL(IF("NBA"=$A:$A,ROW($A:$A)),100),1),"NBA",OFFSET(B$1,0,0,SMALL(IF(&...

0
votes

### Excel VBA - Copy data from multiple sheets into a table

This is easy to achieve with a macro as follows:
Option Explicit
Sub copyrange()
Application.ScreenUpdating = False
Dim DestRng As Range, r As Long
For r = 2 To 7
Set DestRng = Sheet1....

Top 50 recent answers are included

#### Related Tags

worksheet-function × 7819microsoft-excel × 6964

microsoft-excel-2010 × 1613

microsoft-excel-2007 × 781

vba × 561

microsoft-excel-2016 × 429

microsoft-excel-2013 × 424

spreadsheet × 208

vlookup × 202

conditional-formatting × 171

google-spreadsheets × 148

macros × 133

date-time × 113

libreoffice-calc × 106

countif × 105

array × 96

pivot-table × 78

microsoft-excel-365 × 65

cells × 61

date × 59

libreoffice × 57

openoffice-calc × 57

microsoft-office × 56

lookup × 53

charts × 52