# Questions tagged [worksheet-function]

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

**0**

votes

**1**answer

8 views

### How to make addresses populate from one spreadsheet to another

I have two excel sheets of store #'s/event details and another one of all the store number addresses. Not every store gets an event so just copying and pasting all the addresses wouldn't be an option. ...

**0**

votes

**3**answers

15 views

### Excel saying too many arguments for IF/AND/MONTH function

I need it to produce orders that are in Canada or Mexico, over $1,000, and in the month of July. The formula I am trying to use is:
=IF(OR(A2="Canada","Mexico", IF(C2>1000, "Ship by Air", IF(...

**0**

votes

**1**answer

16 views

### How to move values from one column to another based on matching values from other columns

Example of what I'm trying to do:
Hopefully the attached image helps you understand what I'm going for.
I have 2 massive Excel files, one with names & income in the column next to them and ...

**0**

votes

**0**answers

42 views

### How do I get one cell to reference two other cells, so its selections change off those two?

I am making a write up sheet for my company for employees. They want me to make it as simple as possible to stop errors. I need help with the employee name selection. Basically, there is 3 positions (...

**6**

votes

**4**answers

1k views

### Combining first two letters from first name and first two letters from last name

I have a spreadsheet of usernames.
The first and last names are in the same cell of column A.
Is there a formula that will concatenate the first two letters of the first name (first word) and the ...

**0**

votes

**2**answers

22 views

### Finding the difference in quantity of matching items from two sheets

I have two different sheets in Excel with two columns in each sheet. The first column contains the name of the item and the second column the quantity.
How do I get the difference in the quantities?
...

**0**

votes

**0**answers

15 views

### Drag-to-duplicate row, preserving data underneath, in Calc

I have a spreadsheet in LibreOffice Calc that I use for invoices. In this is a row that lists task and cost. When I select the cells in this row, I can drag the lower-right corner and duplicate the ...

**0**

votes

**1**answer

33 views

### Identify gaps in a sequence in one cell - Excel

I have a list of items in my Excel sheet that are numbered, together with a total number, in the following way:
Example:
| Column A (available) | Column B (missing) | Column C (total)
___|...

**0**

votes

**0**answers

37 views

### EXCEL: SUMIF In Array Formula Causing Errors (Bug?)

I have noticed that SUMIF does not play nice with array formulas.
To save on convoluted explanations of my motivation, here is a sample that demonstrates the issue:
=SUMIF(ROW(INDIRECT("1:10")),"<...

**1**

vote

**2**answers

37 views

### Comparison of strings containing inequalities in excel countif() function

I'm analyzing some survey data in which the responses are in the form of a string containing inequalities, e.g.
a) < 5
b) 5-10
c) 11-15
d) > 15
I'm attempting to tabulate responses using the ...

**0**

votes

**3**answers

61 views

### Sum rows based on criteria in columns

I'm stuck with a problem,
I have two sets of data like below:
How can I find if the sums of column "a" based on ID in Table 1 are equal to column "b" based on ID?
I'm assuming i need a Sumif and ...

**-1**

votes

**1**answer

42 views

### What is Excel #N/A error abbreviation, looking for?

In MS-Excel we have below functions return errors:
#N/A #REF! #NAME? #DIV/0! #NULL! #VALUE! #NUM!
I think about what is the complete error massage, which each one are represents?
Of ...

**0**

votes

**1**answer

35 views

### Excel - Search for a string then add some values (Entered to many arguments)

I have 5 sheets with country data, I want to lookup the country name on 4 separate sheets within 4 separate ranges then take a value from the row and add all those values.
I was getting #NA as a ...

**-1**

votes

**0**answers

16 views

### Leader board rank system in excel [on hold]

Can anyone tell me how to build a formula in Excel, that will allow to me to take team points and rank from first to last?

**-1**

votes

**0**answers

21 views

### Excel Functions [on hold]

I'm attempting to make a calendar for auditing purposes. My goal is to input all data on one page, then have a list of upcoming audits on a different page
So on the data page, I have:
Company - ...

**0**

votes

**1**answer

19 views

### Return and List different values in different cells

enter image description here
I working on writing a formula that displays all users with status yes in different rows. Attached a small picture of sample data and how the result should look like. ...

**1**

vote

**1**answer

35 views

### How do I use Excel to produce a table showing what plant species are likely to be present based on location and habitat?

I'm a botanist and I am trying to produce an Excel table that filters a very long list of species based on location and basic habitat.
I have created a master table with 200 rows for each species ...

**0**

votes

**0**answers

16 views

### Excel: Is it possible to set some cells on a different z-index? [closed]

In order to ease a task, I would require to set a portion of an excel sheet onto a different "z-index" (took from CSS z-index logic).
Basically, my requirement, in order to be satisfied, is to being ...

**0**

votes

**3**answers

59 views

### Finding highest numerical value from a range of hybrid data in Excel

I have a row of 6 alpha-numeric values as in the picture. I need a formula that will determine the three highest numerical values across each row, and then display the letters associated with those ...

**0**

votes

**1**answer

36 views

### Count the number of times a combination of two values occurs in a cell

I want to count the number of time a combination of two values occurs in a cell. For example:
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,NCELLRNCID=1,NCELLID=2;
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,...

**0**

votes

**1**answer

27 views

### Removing rows in an excel worksheet if duplicate is found in another worksheet

I have two worksheets: "compiled" and "firstpickup". What I'm looking to do is delete rows in the sheet "firstpickup" if the value in column A can also be found in column A of "compiled". "compiled" ...

**1**

vote

**1**answer

27 views

### Latest Date from Attendance Sheet where attendance is noted as “P”

I have a list of names in Column A starting in cell A2
Each column heading starting from cell B1 contains individual dates
As a person attends an event I enter P into the appropriate cell ...

**0**

votes

**1**answer

29 views

### How to compare two table with duplicate key reference

I have two worksheets, and there are many columns and rows inside.
The same key reference is " order number" with two worksheets.
But unfortunately, the order numbers are duplicate.
is there any way I ...

**0**

votes

**1**answer

29 views

### Combining multiple excel formulas

I have three different formulas that I would like to combine into one formula;
In cell I2 : =IF(ISNA(MATCH(CONCATENATE(A2,D2),INTRAFREQNCELL!E:E,0)),"NO","YES")
In cell J2 : =IF(ISNA(MATCH(...

**0**

votes

**1**answer

20 views

### I need to apply two formulas in one cell (one formula is logic one)

I’m currently working on a Risk Register document in Excel and have populated 2 separate columns: 1) Risk Impact and 2) Risk Probability
Both of those fields can have values from 1 to 5. It depends ...

**-1**

votes

**0**answers

47 views

### Overlap in no of days calculation [on hold]

I work at a rental company and was tasked with writing up a formula that calculates number of days in a month our various units are on rent which will be used to calculate revenue made on the unit for ...

**1**

vote

**1**answer

22 views

### Excel: Sum [range] if cell equals value of another cell

| A | B |
1 | Value | Input |
2 | 1000 | 1000 |
3 | 3000 | 3000 |
4 | 5000 | 150 |
5 | 10000 | |
6 | 15000 | 3000 |
7 | 20000 | 20000 |
-----------------
I'd like to know if ...

**0**

votes

**0**answers

18 views

### How to retain internal connections (existing worksheet) in excel

I am having an Excel Master File, v1 and the data source for my list boxes and multi-list boxes are pointing to an external workbook, v2 instead of internally.
As a result, my conditional formatting ...

**0**

votes

**2**answers

38 views

### How can I use SUBSTITUTE to substitute an apostrophe?

I want to substitute spaces and apostrophes with hyphens. I use this formula.
=SUBSTITUTE(SUBSTITUTE(C129," ","-"),"'","-")
This has replaced the spaces but ignores the apostrophes.
I then tried
=...

**-1**

votes

**0**answers

52 views

### Adding or Subtracting based on 3 variables

This may be simple but I'm new to formulae in Excel.
I've got two sets of data that both have Dates in one sheet. One set has repeated dates for every Type and every State because it's a cumulative ...

**-3**

votes

**2**answers

65 views

### Excel: how to count and group rows

I have an Excel file with Column A and Column B. Both have people names (Elizabeth, John, Michael and Robert).
I need to count each name of Column A and count each name of Column B. And then I need ...

**0**

votes

**0**answers

20 views

### How to enter an array formula within another formula

I have an Excel sheet, which holds timings for reports (when were they started, first handled, still open or already closed, ...).
For clarity in the Excel formulas, I've given a name to all columns, ...

**0**

votes

**1**answer

34 views

### How does Increase decimal and Decrease decimal work in Excel

How the increase decimal operation is performed in Excel and how they done it?
Case 1 :
I enter a number in a cell whose number of digit is less than 11.
For instance A1 cell value be : 123
If I ...

**-6**

votes

**0**answers

47 views

### Please help me check whether this excel IF function has too many arguments?

=IF(S9=1,IF(P11=1,IF(AD5=1,AH5,IF(AD5=2,AH6,IF(AD5=3,AH7,AH8))),IF(P11=2,IF(AD5=1,AI5,IF(AD5=2,AI6,IF(AD5=3,AI7,AI8))),IF(P11=3,IF(AD5=1,AJ5,IF(AD5=2,AJ6,IF(AD5=3,AJ7,AJ8))),IF(AD5=1,AK5,IF(AD5=2,AK6,...

**0**

votes

**2**answers

29 views

### Combining Formulas add percent and roundup

How can I combine the following formulas?
=f4*0.54
and
=roundup(d4,0)
In cell D4 I have the formula =f4*0.54, which gives me our cost from an MSRP cost that I entered in cell F4, but the cost that ...

**0**

votes

**0**answers

49 views

### excel formula to find date between two columns

I'm newbie to excel. So please forgive me if anything found silly.
I have two sheets with couple of similar columns, rest everything is different. Now what I was doing is:
In first sheet (say sheet1)...

**0**

votes

**1**answer

38 views

### EXCEL - Nested Ranges for references

thanks for reading my question.
I'm trying to find price points for various sizes of product packages. I have a data range on Sheet1, A1:Z102, with the top row as mass of the packages (B1:Z1), and ...

**1**

vote

**3**answers

91 views

### Search a field based on two or more columns

I want to output the professor field based on the input in the yellow fields. The input will be the possible sub_code of column D OR column G. I've tried VLOOKUP(...) and INDEX(...,MATCH(...)), but I ...

**3**

votes

**1**answer

67 views

### Excel: Keep cell containing date the same after being set, without macros

I have a table that looks like this:
What I want to achieve is that when you fill in a row (other than the 'Time' section), the 'Time' column automatically fills itself in. Then, it stays the same ...

**1**

vote

**1**answer

33 views

### Excel: Count cells in matrix with multiple conditions

I have a table table1 with a matrix of n columns [[column_1]:[column_n]] that contain dates or empty fields.
Also there's a column [number] with integers.
I want to count all cells that contain a ...

**0**

votes

**0**answers

14 views

### Vlookup For time range and duplicate removal

My data is like in the picture and like to display it like the sheet.
I tried the Formula:
=SUM(--(FREQUENCY(IF(MOD(Table2[A],1)>=D2,IF(MOD(Table2[A],1<D3,MATCH(Table2[BBB],Table2[BBB],0))),...

**0**

votes

**3**answers

23 views

### How to omit a row in calculations?

I have the following data (the first column is Excel's row number, the actual values of column A are 10, 10, ...)
A
1 10
2 10
3 15
4 27
When making a SUM(A:A) all figures in the column are taken ...

**0**

votes

**0**answers

29 views

### Excel changes formulas (on save?)

Sorry if the following is impenetrable, but I haven't been able to find a similar example:
I have a (large) model that uses lots of range names. Recently, on saving, the formulas are changing the ...

**1**

vote

**2**answers

40 views

### Sum cells based on the value of other cell

I am facing a problem that seems trivial in Excel. What I need to do is simply this:
In short, I have a large file of prices linked to a few people and I need to know how much each person paid. I've ...

**0**

votes

**3**answers

37 views

### How can I get Excel to choose the highest grade credits on my course and display my final grade?

I'm in my final year of university and my final grade is calculated by (0.2*second year grade) + (0.8*final year's 90 best credits). I study one course worth 30 credits and 6 courses worth 15 credits ...

**0**

votes

**1**answer

34 views

### Excel formulae for priority sections

I would like to add the date C6 to E6 (if P1=30 days, if P2=60 days, if P3=90 days, if P4=1 year) and show total in I6

**0**

votes

**1**answer

47 views

### Counting how often two different values appear with the same value in the neighbouring cell

I have an excel spreadsheet with one column for ingredients and one column for recipes. (see on the left in the picture below). I would like to create a matrix with all ingredients (as seen in the ...

**0**

votes

**2**answers

52 views

### Fixing Excel timesheet by time in and time out per user and date

I have this:
which is difficult to read, 'I' means time in and 'O' means time out.
I want to transform it like this:
I want to align each time in and time out to its respective employee ID and ...

**0**

votes

**1**answer

24 views

### Can't get dynamic print area working

I have just finished building a report which will have a variable number of entries and would like the print area to adjust dynamically. The print area needs to run from B2 across to AD2, and down to ...

**-1**

votes

**2**answers

89 views

### Looking for formula to search for particular pattern in Excel

I am looking for formula to search for "words" with a particular letter pattern, like INNSA1 or INMAA4 or INPTPB, where the word starts with IN and ends with 4, 1, 6, or B. The words are always ...