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

**1**

vote

**1**answer

50 views

### Excel 2013, SUMIFS formula

I think I need to use a SUMIFS function for this but I am not positive. I have a list of codes:
All of these codes are in a category of downtime, each code also has minutes 5,10,15,40,65 etc. My ...

**0**

votes

**1**answer

61 views

### Excel: transforming an indexed sheet with many columns to two columns

I have an excel sheet with rows like this:
Fruit | Apple | Banana | Grape | Peach
Vegetable | Cabbage| Lettuce| Carrot|
and would like a two column output duplicating the index (first column):
...

**0**

votes

**1**answer

39 views

### VBA - auto-populate date when data entered in cell

I have a tracking spreadsheet where I'd like the date a cell is completed to auto-populate in the next column. I found the following code which works for the first (I:I), but I'd like to add the same ...

**-2**

votes

**1**answer

27 views

### Excel 2010 - possible to apply a function to an entire column?

In Excel 2010, is it possible to apply a function (not a formula) to an entire column (without dragging the plus symbol all the way to the bottom)?

**1**

vote

**1**answer

29 views

### Create a formula to find difference between values in regularly spaced rows

Lets say I have 100 rows in column A. That would be 100 cells with a value in each. I only want the value of the difference between every 5 cells. For example, in the first cell, A5-A1, then next cell ...

**0**

votes

**3**answers

47 views

### How do I reference an Excel sheet name in an Excel formula?

How do I reference an Excel sheet name in an Excel formula?

**0**

votes

**2**answers

18 views

### If/Then (# to Text) With Multiple Number Ranges

I am using Excel and trying to create a formula. Part of the formula works, the next doesn't. I'll explain.
Column P is "BPM" (Beats Per Minute) and will have a numeric value, which will be entered ...

**0**

votes

**1**answer

25 views

**1**

vote

**1**answer

47 views

### How can I use if function in excel to count unique numbers which are not greater than given time duration

I have bunch of transaction data containing date, time, ID
like this:
DATE TIME ID
7/6/2015 9:30:22 0266731550
7/6/2015 9:33:19 0266731550
7/6/2015 ...

**0**

votes

**2**answers

35 views

### excel copy values of cell if particular cell in same row is not empty

i have column A with first and last name of a person, column B with place of birth(not important in this case) and column C with employment date.
Column C can be empty.
I would like to copy values of ...

**1**

vote

**0**answers

31 views

### Search in text for column of words and return the word that matches

I have two columns of data, the first (Column A) contains a whole load of text, the second (Column C) contains the "key" data.
I want to see if the text in Column A is matched by any of the entries ...

**0**

votes

**1**answer

20 views

### Excel Drop down lists

I have a drop down list of product names. Each of these products has an assigned group of employees. I would like to select the product name and then have the assigned employees names automatically ...

**3**

votes

**1**answer

32 views

### Extract content from the formula in a referenced cell in Excel 2007

I have a cell A3 that says Name.
Cell F16 contains =A3, which displays Name, just like I want it to.
I need cell F26 to take the information from cell F16 and display the referenced location. In ...

**0**

votes

**0**answers

12 views

### Excel - Pieces of data from large chart sheet 1, auto sort on sheet2

I have a large chart and need to take small pieces of data (3 cells each for 14 rows) and have them stack ranked on the second sheet. I need A21:A34 (Branch Name), M21:M34 (% of Goal) and K21:K34 to ...

**2**

votes

**2**answers

31 views

### Excel: Calculate IF column based on another IF column

I am trying to use Excel to create a scorecard for different companies to use. The product I create needs to be a finished product - when a company representative fills out certain information on ...

**1**

vote

**1**answer

29 views

### Count how many fall within a time range based on specific text

I am trying to produce a formula that counts how many times and individual registers a time between a certain time range. The table below is part of the data. So, for example, I need a formula to ...

**0**

votes

**1**answer

37 views

### Count rows matching criteria for every row in excel

Hi here is a sample spreadsheet:
+--------+-------+-------------+-------------------+
| Animal | Count | Animal Type | Animal Type Count |
+--------+-------+-------------+-------------------+
| cat ...

**0**

votes

**1**answer

19 views

### SharePoint Formula =IF(ISBLANK)

Is anyone able to tell me how to fix my formula. I have tried everything and I continue to get an error message. I am running out of options.
I am trying to hide the SharePoint default date of ...

**1**

vote

**1**answer

19 views

### Inserting formula through VBA in “A1” reference style

I insert this formula through VBA,
Cells(1, 1).FormulaR1C1 = "=LEFT(A2;5)"
But the formula which gets inserted ends up with apostrophes.
=LEFT('A2';5)
This works perfectly with the R1C1 ...

**1**

vote

**1**answer

13 views

### Syntax for proper use of INDIRECT function using SUMIF in Openoffice spreadsheet to specify a functional range

Similar to others who have asked related questions, I have a spreadsheet sheet which summarizes different expense categories on a different sheet (called PC activity)in the same file. PC activity is ...

**0**

votes

**1**answer

34 views

### Excel Formula with conditions

This feels like it should be simple but my head is hurting now and I'm struggling.
I need to add a condition to an existing sheet which already contains formulas and references to other sheets.
...

**0**

votes

**1**answer

42 views

### Searching for multiple sub-strings within a single parent-string

I've been searching all day for a similar question to mine. Many questions come close, but my questions is a bit different:
I have a list of last names in Column A. In Cell B1, I have someone's ...

**6**

votes

**4**answers

257 views

### Average numbers if another column contains a certain substring text in Excel

I'm looking for a way to average the numerical numbers in one column, say B, only if the string in column A contains a certain text.
For example:
(In column A) (In column B)
Hello table . . . 1
...

**0**

votes

**0**answers

26 views

### Is this possible to generate excel combo box based on cell value?

As you can see, Cell C2 got 1,2,3,4. I assume this is a student id. So, I would like to generate a combo box in Cell B8, using Cell C2's data, separated by ,. So, in this case, the combo box should ...

**-1**

votes

**3**answers

26 views

### In excel, how do I group duplicates according to whether or not they meet a certain condition?

I have a list of people with each row showing a person, and their usage of a platform in percentages. if a person uses more than 1 platform, they will appear in multiple rows, each for a different ...

**0**

votes

**2**answers

29 views

### Counting number of each item assigned to customer with unlabelled columns

I have a table in Excel as follows:
1 2 3 4 5 6 7
Customer1 Z U A C D A A
Customer2 B D J S F D B
Customer3 S A C A A B B
Customer4 X H A C D A A
...

**0**

votes

**1**answer

37 views

### How can I get an average of times in Excel?

I Have a column of cells in excel of format hh:mm (The day is NOT included) like:
19:08
19:08
19:40
20:10
1:45 (Any Times before 12:00 noon are considered NEXT DAY)
one (or more of these) is after ...

**1**

vote

**3**answers

65 views

### Summarize tables of several sheets into one final Table Excel and include new sheets in summary

I have got several excel sheets, every single one of them containing a table for user information and one last sheet which contain the sum of all the previous tables. Since my table is very big, is ...

**0**

votes

**1**answer

52 views

### Dynamic array/matrix generating within formula

The following formula:
=TRIM(A1:A20)
would make no sense; you typically should decide a column, write =TRIM(A1) on first cell and copy until =TRIM(A20) ... BUT ... suppose you want/need to use it ...

**1**

vote

**3**answers

64 views

### How can I automatically organize data from a text file in a spreadsheet?

I have a text file like this:
and I would like to organize into Excel like this:
I could do this by hand manually for two data entries, but I would like to be able to automate this for a larger ...

**2**

votes

**1**answer

64 views

### Use Excel to match data from a cell on a worksheet, then copy data from its neighboring column and paste on a different worksheet

I have a spreadsheet with multiple names and phone numbers next to them in each Sheet 1 - Sheet 5.
Each Sheet has about 900 names on column A and their associated phone numbers next to them on column ...

**4**

votes

**2**answers

390 views

### How to count if there's a cell with certain restrictions using Vlookup?

I'm finding if there at least one cell with the first character equal to "!"
Formula I have used without success:
=Vlookup("!";Left(N2:N49;1);1)
Why doesn't it work? And what do I have to change ...

**0**

votes

**1**answer

31 views

### True and False 2 sheets

I have to input a value in column I in sheet 1 from excel and in the column "D" from sheet 2 it needs to appear true or false. If there's any value in I than it should be true in "D" otherwise ...

**0**

votes

**1**answer

54 views

### Excel Formula - Match

Can someone recommend a way to write a formula that will achieve the following. I can achieve these things individually but stringing together seems to be difficult.
I have a list of orders with ...

**0**

votes

**1**answer

30 views

### In excel how could I generate a row of data from another sheet?

In excel I have a sheet named "sales", and another one named "factor".
In factor I have some fields for factor ID, customer name, product code, quantities and price.
I want when I add this data to ...

**1**

vote

**1**answer

21 views

### Excel - pivot date values with max function

I am trying to pivot date values in a pivot table in MS Excel. I am getting incorrect date values. I do not want "Grand Total" as they do not apply for dates. Is it possible to do such pivots in ...

**1**

vote

**2**answers

31 views

### How do I use IF in MS Excel-Mac 2011 for 3 variables?

When I type A, B or 0 in one cell, I want to see in other two cells (separately) to show
2,1 if I type A
1,2 if I type B
0,0 if I type 0
Can someone help me how to do this?
I currently only have ...

**0**

votes

**3**answers

45 views

### Excel Formula issue

I've been trying to write a formula to help me with rostering. It seems to work if I put the first few parts in, but after that it says "The formula you typed contains an error."
Can anyone see the ...

**2**

votes

**1**answer

46 views

### How to make a formula-based cell read-only in MS Excel?

I'm trying to create a unique ID for each row in Excel, based on the formula [RowNum + X] (X is given).
The formula autocompletes on each new row, based on Excel Tables.
After creating the new row, I ...

**1**

vote

**1**answer

50 views

### Checking column for value. If true, check same row in another column and count if not blank

I have a spreadsheet of employees. Column A is a list of Dept types (sales, HR, payroll, etc). Column B is the date their appraisal is completed, or blank if incomplete.
I would like a function that ...

**0**

votes

**3**answers

99 views

### How to keep a running balance of payments vs. billings in Excel

I have a row of cells with 12 Monthly columns, G4 (January) through R4 (December), in which I will input the amount paid towards a monthly billing. [Note that an early comment discusses multiple years ...

**2**

votes

**1**answer

120 views

### Is it possible to reference two cells in Excel to find a third cell?

I have a spreadsheet that has a decent amount of data on it. I need to return some of that data to certain cells. The data that I need to return is always near a cell with "Attached Components" in it. ...

**0**

votes

**1**answer

24 views

### Deleting all cells in row after blank space

I was just wondering if there is a simple way to, for each row, move right and check if there's a blank cell and if so delete everything to the right of it. Can this be done with a formula?

**1**

vote

**0**answers

35 views

### Excel lookup value not on same row

What i`m looking for is that the data will be filled automatically from data table by lookup value in not same row.
See the picture
or xls example https://dropmefiles.com/BDlHe
1st table lookup ...

**0**

votes

**1**answer

16 views

### Excel- averaging subsequent cells using first unused cell in column

Good morning,
I need this output to be on a separate sheet, but essentially the problem I am trying to solve can be explained as follows.
I am trying to figure out how to average cells in a column ...

**0**

votes

**1**answer

43 views

### How to compare and sumproduct two cells value and use multiple criteria in IF in Excel 2013

I want to compare the month value (=TEXT(A1,"MMMMMM")) from sheet A with a cell value (JULY) in sheet B using an IF statement.
If the months are same then I need a sumproduct of the cells in sheet A.
...

**1**

vote

**1**answer

25 views

### SUMIF function with rules inside the interval

Is it possible to set the condition of excel's SUMIF function inside the interval where there are the value to sum?
In the picture above I have to sum only the value in the column BU that are > ...

**0**

votes

**1**answer

43 views

### Concatenate two columns, look for duplicates copy single row to new worksheet

I have an Excel or CSV file with 5,000 rows and 20 columns.
I want to concatenate Column 6 and Column 8, look for duplicates and then copy only one of the duplicates into WorkSheet2.
For Example:
...

**0**

votes

**1**answer

68 views

### Count based on multiple alternate conditions in Excel

I need a formula to count any trailers that are due in but do not have a cell that says "yard" or have a date next to that trailer. Please see screenshot. I tried the COUNTIFS formula and was ...

**1**

vote

**1**answer

47 views

### Formatting date from decimal in Excel [closed]

How do I format a decimal value like 1.30 as "1 year & 3 months" in Excel?
The value needs to remain the same but just needs to be displayed as 1 year & 3 months.
I need to do this just ...