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

**0**

votes

**1**answer

77 views

### Excel 2013 Averageifs

I want to calculate the average of time in a column of cells if the time falls within a range in another cell.
E.g. If a patient comes in between say 6 and 8 pm I want to average data in another ...

**0**

votes

**1**answer

77 views

### sumif(s), sumproduct for many to many relationship

I am looking for a formula/function that will allow me to essentially SUMIF(s) across a BOM. I know my part to FG relationship (Part, FG) and I know how many FG's I need(FG, Qty).
How can I ...

**0**

votes

**1**answer

50 views

### Error outputting a string to cell

I am using a vba macro to automate part of an excel spreadsheet. The following line keeps giving me a runtime error 1004# application or object defined error
Sheets("Material").Last.Offset(0, ...

**0**

votes

**1**answer

123 views

### How to automatically delete rows that contain certain numbers in the ID column

I have a column of numbers in another workbook and I would like to use that set of numbers to delete rows in the main workbook. I will give an simplified example of what I'm talking about below. My ...

**1**

vote

**2**answers

123 views

### How do I allocate different text cells in a row a number and then sum total that row

I have a spreadsheet setup to monitor absence in the office. The columns are the days of the month 1 - 31 and rows are the month/employees
Mon Tue Wed Thu Fri
January 1 2 3 4
...

**0**

votes

**1**answer

51 views

### Deleting rows that contain a keyword in the column

I have an excel file that has a id as one of the columns, each id has multiple entries and follow a set of survey questions. I need to delete all of the rows that have that keyword (which is in a ...

**0**

votes

**1**answer

913 views

### Excel Formula Issue Countif to Sumproduct

Having a nightmare at the moment with some formula's I've been asked to 'fix'. It would seem the users expected them to work with closed source spreadsheets but they don't. So they end up having to ...

**-1**

votes

**1**answer

28 views

### Excel formula: identifying highest weights

I need some help with the following assignment:
Task:
In the product Weight tab, you can see each product is weighted with a value per customer. Please create a Product Code for each customer based ...

**0**

votes

**0**answers

11 views

### summing values associated with names in multiple columns

I'm trying to sum up total values with names. There are 8 total columns rotating between a list of names and then values. I believe a sum if function would work, but I'm too novice to find the right ...

**0**

votes

**1**answer

75 views

### Excel 2010 Formula of 3 variables with 3 results

I haven't been able to figure out how to combine these logic & date formulas in 1 cell.
My parameters would be:
A2 will always have a manually entered date in it
B2 could have one of three ...

**0**

votes

**1**answer

46 views

### Embedded IF(OR(IF) statement

I have made a formula that basically looks for a string of text inside a cell and if it finds it puts it in another cell. I needed to check three cells so I did three separate formulas (because I'm ...

**0**

votes

**1**answer

246 views

### Excel: portable formula to reference data on previous (relative) worksheet

I need to create a fairly simple Excel spreadsheet (workbook). I need 12 sheets (tabs), one for each month.
Each sheet references cells in the previous sheet. For example, the "May" sheet is ...

**0**

votes

**0**answers

75 views

### Convert COUNTIFS formula to SUM(IF) to enable external workbook references?

I need to convert the following formula to one that uses SUM(IF), as I am given a #value error when the external data source is closed.
...

**3**

votes

**1**answer

539 views

### Replace cell references with values without F9 using macro

This is an Excel macro question. Given a series of formulas such as the following:
= A1 + A2
= B2 + B2
etc., ...
I would like to replace each of these cell references with the values without ...

**0**

votes

**1**answer

72 views

### How to I use SUMIF or SUMIFS to automatically add the values of different cells when they meet they meet multiple criteria

Basically I have a spreadsheet with three criteria columns.
I would like to be able to recognize duplicate HTS codes that also have the same country of origin and add the sum of those values, which ...

**0**

votes

**1**answer

909 views

### How do I merge data from other excel files to one master excel worksheet?

I have an uniform ID system throughout all the files, except with multiple entries and differing data named columns. I would like to create a master excel sheet with specific data columns from each ...

**0**

votes

**1**answer

363 views

### Hide columns based on content (sum of rows)

I'm trying to hide columns based on the sum of selected rows (i.e., cells) in those columns.
For example - if the sum of E9:E12 is 0, then hide column E.
Additionally, and probably a little harder - ...

**0**

votes

**2**answers

129 views

### Selectively find duplicates across columns

Column A has the product ID. There are 102 other columns with other data. Column A will be unique unless some data was changed. In that case, there will be 2 rows with 99% of the columns are ...

**0**

votes

**1**answer

29 views

### excel - inserting a value based on a range of values

I need to link a batch code to a specific unit number. The batch codes are the same for a range of unit numbers. The first table below shows the unit numbers; the “?” marks are where I need to enter ...

**2**

votes

**2**answers

54 views

### dragging to populate cells in Excel

I am aware that you can fill the cells in Excel as a series by dragging the square in the bottom right corner.
for example typing "1" in Cell "A1" and dragging the box down to "A10" will give you a ...

**1**

vote

**3**answers

55 views

### Is there a quicker way to enter formulas?

In the firm I currently work in we have a very inefficient and time-consuming process for entering formulas. For example:
Y1=CHOOSE(B3,'[Flacflow balancesflacflow.xlsx]Data'!$A$77, '[Flacflow ...

**1**

vote

**1**answer

59 views

### Excel: Keeping references/links when transposing

I have a worksheet in where I have table (let's call it the summary table) with references to a another table (among others) which is 2x40 (rows x columns). I would like to tranpose this into a 40x2 ...

**0**

votes

**1**answer

56 views

### Copy the content of one cell, and insert within 2 characters, in the middle of a text content from another cell (Excel)

I am trying to batch copy the content of the cells in column A, and insert it within the "><" of the text in the column B. The result should look like the output in column C. Here is an example:
I ...

**0**

votes

**1**answer

42 views

### Trying to get an alert if three seperate cells do not match

Hi I am trying to put 2 formulas together in one cell
The first is to give a sum, i.e. : =(D3+F3+H3+J3)-(C3+E3+G3+I3)
The second is to check the resulting number above against a number total in 2 ...

**0**

votes

**1**answer

433 views

### Get the index of selected value in Excel drop down

I have a drop down list generated in Excel from row values. I want to select from that drop down, get the index of that value in the drop down array, then use that for an offset value to select the ...

**0**

votes

**0**answers

217 views

### Return value from table using Multi-dependent drop-down list

I am trying to obtain the value of a column header (or topmost row value for a specific column) based on the selection of properties from 2 drop-down lists.
...

**0**

votes

**1**answer

241 views

### Hide sheets in shared excel workbook.

I have a shared workbook on which I have hidden some of the worksheets prior to saving.
I have closed the workbook. If another user opens the workbook the worksheets I had hidden are visible. If I ...

**-1**

votes

**1**answer

60 views

### Excel searching column and copying the cell next to it

i need to find the value from column C in the row B, and copy the value from found result in column D. If it's possible with formula and to copy formula to all cells in column D ? without VB?
Thanks ...

**1**

vote

**0**answers

26 views

### Naming worksheets automatically [closed]

I have a spreadsheet that I input data into everyday of every month; each worksheet represents one day of the current month. I have all the formulas I need but each month I have to change the ...

**0**

votes

**1**answer

99 views

### Excel Formula Challenge

I have a fairly specific Excel problem and I am hoping someone is savvy enough to come up with a working formula for me. I am using Excel 2011 for Mac btw.
So, I have a list of about 134 ISBNS, for ...

**0**

votes

**1**answer

39 views

### count number of cells that meet certain criteria between two other cells with certain criteria

I am looking for a way to count the number of cells meeting a specific criteria that are located between two other cells. For example:
A B C D E
151867377 01:03.5 ...

**0**

votes

**1**answer

10 views

### Cell to relate to information depending on infrmation in another cell

I hope you can understand what I mean. I have tried to draw below to help explain.
I need column d to have the information in column a1 and column h to have the information in a2- Keeping it in the ...

**0**

votes

**3**answers

42 views

### Is there a way to combine duplicates within a large range in Excel?

I have a large (1.7 million rows) table of sales data on a sheet in Excel, many entries of which are identical other than a unique ID and a timestamp. I’d like to consolidate this data by combining ...

**0**

votes

**1**answer

34 views

### I am trying to match based on a dynamic cell reference with the match function

=MATCH(B$4,CONCATENATE("'",A5,"'!$E:$E"),0)
The above formula result is #VALUE!
Cell A5 has the value Sheet1, however, after testing this will change to a date e.g. 01-Jul-14
What am I doing ...

**0**

votes

**2**answers

58 views

### Excel - Formula to get total number of worksheets

I have 100+ sheets, it is hard for me to count them one by one.
Is there a one line formula to get the total number of sheets? The CELL or ADDRESS function perhaps?
I remember using something like ...

**2**

votes

**3**answers

253 views

### How to sum varying groups of numbers in excel?

I have a group of numbers:
A1 12
A2 13
A3 3
A4 5
A5
A6 1
A7 8
A8 9
A9
A10 2
A11 19
A12
and so on...
I would like cell B5 to show 33 (sum of A1 to A4), B9 to show 18 (sum of A6 to ...

**1**

vote

**1**answer

7k views

### SUMIFS using “greater than or equal to” with text values

Using the following simplified example, I want to calculate the sum where Category="Graduation" and Date >= 1986-05-01.
Category Date Count
Graduation 1985-06-15 1
Graduation 1985-09-12 2
...

**3**

votes

**1**answer

226 views

### Excel 2010: Formula for Sum and Return Date

I have a workbook with a Summary sheet and a Data Set sheet with a linked table containing 700k+ records of financial data on investments. (In my particular case, it is real estate properties; but ...

**2**

votes

**1**answer

286 views

### How do I convert dates from Open Office format to MS Excel format

I am trying to save a file in Libre Office as an excel file but Excel sees the data displays it as a negative number using a series of ###### symbols.
I believe the problem is to do with the two ...

**0**

votes

**1**answer

17 views

### highlighting cells and getting a sum

If you highlight two cells, at the bottom of the screen it will tell you the number of cells you highlighted as well as it will add the cells together and give you a sum. This is working for me but ...

**0**

votes

**1**answer

74 views

### Combining IF and SIGN functions in Excel

So I'm trying to do something in Excel, in our lecture notes the teacher uses it, without explaining how.
For reference, this is the bisection method to find roots of an equation.
So I have 4 ...

**0**

votes

**1**answer

356 views

### Pie Radar Chart outline series not working

I have been trying to create a Pie Radar Chart as described in answer to this question.
Everything works until I get to the section that describes inserting a series to create the chart's outline:
...

**0**

votes

**1**answer

1k views

### Microsoft Excel 2007 - Drop down list that grabs multiple columns from another sheet

I've been scouring the internet for this but can't seem to find a solution.
Let's say I have 2 Sheets - "Items" and "Inventory".
Inside "Items", I have 3 columns:
Item Name
Item Type
Item Cost
...

**1**

vote

**2**answers

360 views

### Excel 2010: Calculate full months between dates

I have dates in two columns and I would need to calculate amount of full months between two dates.
I export the spreadsheet from SAP ERP in .xls format but opening with Excel 2010. DATEDIF function ...

**1**

vote

**3**answers

3k views

### Calculate number of Sundays in the month

In Microsoft Excel I am looking to determine the number of Sundays in any given month. I am putting together a budget spreadsheet and some of the sub accounts get extra funding when five Sundays are ...

**0**

votes

**2**answers

457 views

### Excel - Include cell in column count if previous cell in row contains certain text

I have searched for this but I have not had much luck. What I would like to do is below:
A B C D E
1 ob ob
2 ACR ob
3 ob
4 ACR ...

**0**

votes

**1**answer

114 views

### Edit current cell via keyboard?

In Excel for Macintosh 2011, when the cursor is in a particular cell of a spreadsheet and you wish for the focus to move to the edit line for that cell where the formula is displayed, is there a ...

**0**

votes

**1**answer

293 views

### Formula to find items in Excel and determine total quantity of each item

I have a steel cut list in Excel. There are over 1000 rows of information and probably 100 different part numbers. Is there a formula that can find the total quantity for each part number?
I am ...

**0**

votes

**0**answers

13 views

### How to use a value in a cell to reference another workbook? [duplicate]

I have a list of names for workbooks and their directory in a worksheet. I want to create a formula where this...
='C:\Users\USER\Desktop\[Book2.xlsx]Sheet1'!$D$6
I want to replace the ...

**1**

vote

**3**answers

161 views

### How can I have a cell flash green or red dpending on whether the updated value is higher, or lower? - In Excel

I am running an excel spreadsheet. Some of the values are being fed in from an external source. In this case, it is the price of crude oil.
The cell is updating in real time.
I would like to have ...