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

**1**

vote

**1**answer

46 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

30 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

30 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

19 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

30 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

12 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

246 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

27 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

35 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

64 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

62 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

60 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

389 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

29 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

44 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

40 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

96 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

114 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

32 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

41 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

41 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 ...

**1**

vote

**1**answer

107 views

### Auto-populate values based on multiple dependencies in Excel

I have three columns with information filled in already. The value in Column C is dependent on two identifiers from Columns A & B (i.e., George is always the seller of Apples in America, but Sarah ...

**1**

vote

**1**answer

90 views

### LibreOffice Calc equivalent to Excel “Evaluate Formula” feature

Excel has an "Evaluate Formula" feature (it's located in the Excel Formulas ribbon among the Formula Auditing group, as shown in the picture below).
Is there an equivalent to that Excel feature in ...

**4**

votes

**0**answers

50 views

### Use functions like Countif, Countblank etc. to refer to discontiguous ranges.

Is there a way to use array formulas or some other feature to essentially have an excel function that means.
=Countblank({A1,A4:A6,A112:127,B29:B38}) etc.
Essentially, I want to refer to multiple ...

**0**

votes

**1**answer

35 views

### Excel spreadsheet formula to sum a column

I'm trying to create a formula that sums a total of only the trailers that are in the yard but don't have a date unloaded next to them. Please see the link for a screenshot of the spreadsheet I am ...

**0**

votes

**1**answer

43 views

### Count continuously until cell value changes

I have a work sheet with data like so:
A
A
A
B
B
C
D
D
What I'd like to do is count each occurrence of A, B, and so on like so:
A 1
A 2
A 3
B 1
B 2
C 1
D 1
D 2
Is ...

**0**

votes

**1**answer

31 views

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

I'm cleaning a dirty directory. A few (just a thousand or so) records have their postcode messed up, either blank or containing other address data. Usually that means city or county.
A Postcode, ...

**2**

votes

**2**answers

24 views

### How to sum two categories within a single column and show the totals to the side of the column?

I have a list of trailers with a column to the side that says if the trailer is full or a pup trailer. I can't figure out how to make the column total how many of each type of trailer and show the ...

**-1**

votes

**1**answer

33 views

### Multiple IF AND statements excel

I want to know if it's possible to have multiple IF statements in one cell.
What I am looking at is this. I have text in Columns A and B. What I want is to check whether Columns A and B match ...