FAQ Database Discussion Community


Excel - Replace offset formula with actual cell reference

excel,excel-vba,excel-formula
I have a table that is pulling thousands of rows of data from a very large sheet. Some of the columns in the table are getting their data from every 5th row on that large sheet. In order to speed up the process of creating the cell references, I used...

How do I match a date from a list of dates in excel?

excel,excel-formula,comparison
I need to compare two dates to see if they are equal from a list of dates in a column. Eg. Sheet 1 Column A Date 1 Date 2 Date 3 Date 4 Sheet 2 : Row F: Date 5 I want to see if date 5 is equal to...

pulling and comparing dates in excel

excel,vba,if-statement,excel-formula,excel-match
For example: On my first tab Last visit, I have columns, Customer Name, Customer # and Scheduled date On my second tab, All visits I have Customer Name, Customer #, and Visit Dates. Now, this tab shows all of the visit dates (even the last one that is on Last...

Get the maximum values of column B per each distinct value of column A

excel,excel-formula
I have an Excel spreadsheet of the form: A,B X,1 X,5 Y,4 Y,11 X,7 Z,1 I would like to get the maximum value of column B for each distinct value of column A - how can I do it via pure Excel formula? Desired result (order is irrelevant): A,B Y,11...

Is there any formula to create a hyperlink in an excel to another sheet?

excel,hyperlink,excel-formula
Here is what i exactly need to do. I have an excel workbook with many sheets. I need to work on two sheets- Attributes_Master and Domain_Details. Both have a field in commmon - Domain but not in same order. I need to add a hyperlink in Domain column of Attributes_master...

Adding division to current formula

excel,excel-formula
I have my Excel formula found below does this: Scenario: I have three products, product A, product B and product C. I want to be able quickly to calculate the actual revenue. The data structure is in the image. My top-most row is month. For each month I have four...

Attempting to count cells in Excel using multiple criteria

excel,excel-formula
As the title states, I am trying to count across cell ranges on a separate sheet but cant seem to get it to work. Any help would be greatly appreciated. =COUNTIFS(MONTH(original!A2:A58),"1",original!D2:D58,"=East") ...

Checking for different values associated with a similar descriptor entered multiple times in a list

excel,excel-vba,excel-formula
I have 2 sheets in a workbook, one "Summary", the other "Enrolments". In "Enrolments" I have a list of individual entries, outlining what classes a student has enrolled in, all identified by their ID number and course code, for example: A B 1. ID Course Code 2. 123456 225.885 3....

Program flow unexpected behavior

excel,vba,excel-vba,excel-formula,breakpoints
I am running the following Sub, which inside other two subs: Sub SalvaDadosLogin() 1: ThisWorkbook.Worksheets("Registro").range("a11") = loginComdinheiro 2: ThisWorkbook.Worksheets("Registro").range("a12") = senhaComdinheiro 3: ThisWorkbook.Worksheets("Registro").range("a14") = salvaSenhaComdinheio 4: ThisWorkbook.Save End Sub Sometimes when I run this sub my VBE starts executing a user defined function (after running the line 1). Other times...

Dynamic formula or vba to offset a vertical list by one, and move the bottom of the list to the top?

excel,vba,excel-vba,excel-formula
Here is what I am trying to do, I have a sheet that has a list of names with no duplicates that varies in length. I would like to have either with a formula or vba sub, to have the next row copy the original list of names and offset...

Return the list of colored cells based on certain conditions [closed]

excel,vba,excel-vba,excel-formula
I have this in Sheet1: and in Sheet2 I want to lookup and filter the colored data, and the result would be: Goal: to return the list of colored cells Filtering (lookup) conditions: 1. Return the colored cell (copy/paste the exact cell content) from columns J to V from Sheet1...

Checking for the latest version of a contract in Excel

excel,excel-vba,excel-formula
First stackoverflow question, so hopefully I've done this correctly! I've had a good look on various sites ranging from Mr XL to Chandoo, and a good look on here too, and I've not come up with a solution yet. I've got a spreadsheet which tracks contracts, but I need to...

Excel INDEX isn't working with Table reference

excel,excel-formula,excel-2010
I am using Excel from Office 2010. I am trying to get values from a specific row (or, eventually, rows) in the table, found by running a search. I can successfully identify the row in the table that I want to output the data from, but the INDEX function is...

SUMPRODUCT combining partial text and dates

excel,excel-formula
In an excel sheet I am trying to retrieve how many entries of a given name occur within a month. I have the date with my month stored in N48 My spreadsheet would look like: B AE Test 06/04/15 Testing 06/05/15 Test 06/08/05 Name2 06/04/15 Name2 06/02/15 So far I...

Filter information in two columns

excel,excel-formula,match,worksheet-function,input-filtering
I need a hint is solving Excel task. I have two columns with data (let's say column A contains a list of people I have met in May; column B a list of people I am planning to meet in June). Is there a function in Excel which will compare...

Excel Function - Convert unicode to ascii

excel,unicode,excel-formula
Is there any function in Excel (2010) which decode unicode to ascii text? One column in my sheet contains word/sentences in Portuguese. For Example: Esse Jean é feio né Should become Esse Jean é feio né é - This letter in the text is not Portuguese, is there any function...

Excel formula to check for duplicate words

excel,excel-formula
I require an excel formula to check if any words are being duplicated in any column in a row. My sample row is below columns separated by a ','. Column No: E221, F221, G221, H221 Column Text: Sam, John/Sam/Smith, Smith, Kyle Above Sam & Smith names are being repeated so...

How SUMIF function in Excel with brackets and “!” sign works

excel,excel-vba,excel-formula
I have a Macro in my Excel and this Macro writes a formula to one cell in my Excel sheet. The code of my Macro that does this is like below: Range("F10").Select ActiveCell.FormulaR1C1 = "=SUMIF('1'!C2,C7,'1'!C[2])" and the Formula produced for the F10 cell is like below: =SUMIF('1'!$B:$B,$G:$G,'1'!H:H) Can someone help...

Excel SUMIFS formula on using operator inside criteria

excel-formula,sumifs
=SUMIFS(C1:C5,A1:A5-B1:B5,">5") Is the above formula workable in excel Column A and B contain dates Column C contains quantity What i need is When Column A minus Column B greater than 5 then sum all the quantity I understand this is able to do with creating a new column to get...

is there a shortcut to open filter>contains box in excel?

excel,excel-formula,excel-2007
I use filters a lot. Filter > contains option But it is slow because everytime i need to do several clicks. So, my question is, is there a shortcut to open filter, drop down, contains window?...

Get unique values in a column except values filtered from the original column - Excel Formula Only

excel,excel-formula
I have been using =IFERROR(INDEX($F$13:$F$1353, MATCH(0,COUNTIF($X$12:X12, $F$13:$F$1353), 0)),"") and it work perfectly in order to bring unique values from a column with repeated values. However I use a filter on the repeated values column "F" and when I deselect any item from the "F" filtered column I would like to...

Excel return value or run calculation if criteria is not met. Formula needed

excel,excel-formula,formula
This is a fairly simply formula that I need syntax for. I do not know the syntax that Excel will accept. Formula: I want to return a value of "0" if the target cell has a value of "2" or less. If the value is greater than "2" I want...

Nesting IF and OR statements

excel,excel-formula
I am having troubles getting Excel to calculate a formula using IF and OR. I have a spreadsheet where I need the information from K6*I6 to be nested. I need it to differentiate the percentage that's listed in cell K (it will be either 12.5% or 25%) and then calculate...

Issue with Arrays/Match/Index

arrays,excel,if-statement,excel-formula,match
Here is my current formula I use to pull the latest live date for a customer out of a web query. {=LARGE( IF(Table_owssvr_1[HQ Name]=B1,1,0)* IF(ISNUMBER(Table_owssvr_1[Live Date]),Table_owssvr_1[Live Date],0), 1)} B1 is the name of the customer. HQ name is column A and contains customer names. This formula will give me the...

Excel 2010 MATCH function returns an error in conditional formatting

excel,excel-formula,conditional-formatting
When I enter the following formula in a cell it returns TRUE, but when I enter this into a new conditional formatting rule, it tells me I have an error. This is the formula: =ISERROR(MATCH(Table2[Website];Table13;0))...

Average of top 3 values within a specific pivot field

excel,vba,excel-vba,excel-formula,average
I have a table in which in one column I have values that are assigned to special models of hardware. The table looks like this: We need to find an average of three of the highest numbers for each model in that table, for example: We have a model 22PFx4109,...

Excel - list entities (items)

excel,count,excel-formula
Friends, I need to list all the unique entities (items) in a Excel spreadsheet, how to? E.g. **List1** 1 1 1.1 2 a a aa b c c And I need a certain function that returns: **Entities** 1 1.1 2 a aa b c Then I can count the occurence...

Data Reporting in Excel

excel-formula
I have Excel Sheet with some data in it. Link will get you to the spreadsheet. I need to build a report that extract information from each months worksheet. All the worksheet is under same workbook. It can be done with VLOOKUP, INDIRECT and COUNTIFS but I am still new...

Excel Pivot Table Exclude one Row in Calculated Field

excel,excel-formula
In Microsoft Excel I want to have a pivot table that uses a calculated field for a row. I would like the calculated Row to exlude the current year. So I want two summary rows one with current year and one without. The attachment in orange shows my desired result....

Excel OFFSET and IFERROR in a table

excel,excel-formula
I have a table in excel with two columns [RunningTotal] and [Change]. I have a formula like this for [RunningTotal] =IFERROR(OFFSET([@RunningTotal];-1;0);100)+[@Change] Its a table with two columns, one for a running total and the other for the change. The IFERROR is for the first row since it cannot be offset...

Can Not Get My VLookUp In Excel To Return The Requested Data

excel-formula,vlookup,excel-2013
Can Not Get My VLookUp In Excel To Return The Requested Data I am trying to pull data from another sheet based on data selected from a dropdown on the main sheet. All the formatting is "General" =VLOOKUP(F15737,'Location Master'!$A:$J,2,FALSE) It just keeps returning me #N/A...

Using OR in SUMIF Excel

excel,excel-formula
I have a curious question about using the "OR" in an excel SUMIF. Basically, in a SUMIF(A:A,"Cat" OR "Dog", C1:C10) How can I use the OR function in an excel SUMIF function?...

Excel - leave blank if reference cell is blank

excel,excel-formula
I have a table that uses following formula to calculate +357 to display "NO" or "YES" =IF(AND(TODAY()<M3+357),"NO","YES") My problem is; I want this cell to display nothing when the reference "M3" cell is blank. Help would be greatly appreciated....

How do I do a sum of contents in an adjacent cell equal a set value

excel,windows-7,excel-formula
What formula would I use to do a sum of the contents of a range of cells, provided that the contents of an adjacent cell equal another cell. The example I could give is, if I have a set of invoices which quote a purchase order, I want to see...

Array Formula for text search

excel,excel-formula,excel-2013
I have used this array formula to find true/false. The formula is only considering the text in the first cell {=isnumber(search(G2:G7,A1))} ...

INDEX MATCH obtaining values for duplicate names

excel,excel-formula
I have one example table with the following data in Sheet1 with the following random data ------A ----------------- B ----------------------C ------------------------D 1 --First--------------Last-----------------Start Date--------------End Date 2 --John--------------Smith--------------08/08/2014------------01/01/2015 3---John--------------Smith--------------08/11/2014------------17/11/2014 4---John--------------Smith--------------06/06/2014------------23/12/2014...

sumifs on excel linked sql server query with date

sql-server-2008-r2,excel-formula
I'm having no luck being able to do sumifs or countifs on a cell range that is linked to an MS SQL server 2008R2 view when using the date column as a criteria. If it is done on a native table link they works fine but it appears that the...

Merge values in multiple columns into one

excel,vba,excel-vba,excel-formula
I have the following data structure: As you see in column J, I am trying to merge data into one column from columns A & C & E & G. I am using this formula: =IF(ROW()<=COUNTA($A:$A);INDEX($A:$C;ROW();COLUMN(A1));INDEX($A:$C;ROW()-COUNTA($A:$A)+1;COLUMN(C1))) and I get the values in column K as you see. Currently this formula...

how to setup a excel chart with data points

excel,excel-formula
I have a excel spreadsheet that has been exported from a kendo-ui grid. I have zero experience with excel and I need help turning this data into a excel chart. the attached spreadsheet is what i am working with. Every subdivision name is its own series with (sqft,price) points. is...

Sumproduct with multiple criteria

arrays,excel,excel-formula
I'm currently using Sum Product to rank year over year data from largest to smallest. My formula in Column Z is SUMPRODUCT(--(ABS($Y$5:$Y$165)>ABS(Y5)))+1 using the data in column Y. The year over year data is in columns W and X. I want to write a formula that does the same thing...

INDEX&MATCH excel on rows and return SUMPRODUCT

excel,excel-formula
I needed some help with Excel functions as I am not really sure how to achieve this. Basically, this picture shows my data structure: I am aware that I can use Index& Match excel functions together for columns but I am not sure if it is possible use Index&Match together...

Excel - Finding work days in a month between two dates

excel,excel-formula
I have successfully managed to calculate the number of days in a specific month between two dates. However I need to calculate those days as work days within the formula. For example. | ------------- A -------------------- B --------------- C | 1 ------- 11/12/2014 ----- 17/03/2015 ----- 01/03/2015 | 2 A...

How to count multiple instances of words in multiple cells in Excel

excel-formula
I have some cells with multiple names in them as so: I have some more cells below where I need to count how many times each name appears in the column For example, in this column Ben should appear 4 times and Matt should appear 4 times. How should I...

Array formula using multiplication and division across 3 columns

arrays,excel,excel-formula
I have Inventory data that is in the following format: Column D | Column E | Column F Pack Qty | Pack Price | Total Qty This is followed by multiple rows with various numerical values, with the odd blank row. To calculate the stock value of any particular product/line,...

Excel with AND, IF, MAX

excel,if-statement,excel-formula,max,excel-2010
I can't quit figure out what I am doing wrong here, I know how to do the formula in multiple steps but can not figure out how to do it in one step all together. So what I am trying to do here is in column A there is numbers...

Improving repetitive use of IFs in Excel formula

excel,excel-formula
Is there a more efficient approach to this formula? =IF(A1="Texas",1,)&IF(A1="Washington",2,)&IF(A1="Kansas",3,)&IF(A1="California",3,) Simple question, but I'm sure there is a better way then the one I've currently got...

Creating dynamic dropdowns in Excel where values may appear in more than one column

excel,excel-formula
Normally, where the values in the column of a lookup array are unique there is only a need to match the value in the last dynamic data validated list with the value in the relevant column of the lookup array to provide the range of values for the next dynamic...

Formula that will calculate total hours in overlapping date array

excel,excel-formula
I've been trying to solve an issue for a while now and I'm just not getting the results I need. I have a spreadsheet that captures labor hours at a mechanic shop and I want to find out how many hours of labor per mechanic. The system uses open work-orders...

Multiple search criteria within Excel Formula

excel-formula
I have a sheet with the following demo data (yeah the content is german don't mind that) And I need a formula which will search for the criteria in A1 and B1 and returns the respective value out of the matrix E3:M8 For example Search criteria is: X and 2...

Finding a specific cell contanining value greater than the desired value

excel,indexing,excel-formula,vlookup
I'm looking for a way to return a specific cell value from a range based on an initial desired value. It looks like this: DesiredValue typed in cell: | 125| Search Range: line1 RangeA|RangeB line2 1 | 50 line3 51 | 100 line4 101 | 500 line5 501 | 1500...

Sum row based on criteria across multiple columns

excel,excel-formula,array-formulas
I have googled for hours, not being able to find a solution to what I need/want. I have an Excel sheet where I want to sum the values in one column based on the criteria that either one of two columns should have a specific value in it. For instance...

unable to use MIN and IF function in excel

excel,excel-formula,excel-2010
Age Qualification 19 Higher Secondary School 24 Graduate 25 Post Graduate or above 21 Graduate 17 Higher Secondary School I am trying to get Minimum age of graduate student which is 21. The incorrect formula i used is =MIN(IF($B$2:$B$6="Graduate",$A$2:$A$6)) which gives 17 as min value....

Shuffle values to a new row two cells at a time

excel,excel-vba,excel-formula,excel-2007
To explain it in the easiest way possible: | 1 | 2 | 3 | 4 | | 5 | 6 | 7 | 8 | ... needs to look like: | 1 | 2 | | 3 | 4 | | 5 | 6 | | 7 | 8...

Using AND and OR together on the IF function

excel,excel-formula
Hi all I have been trying to fin out how to use AND and OR together on the same formula. I'm trying to resolve the following issue : The person will get a discount only if they are working on the Cerrovial project or if they are working on the...

INDEX/MATCH for closest value to a certain date

excel,date,excel-formula,worksheet-function,array-formulas
In sheet "Dividends" I have a table with dividends sorted by daily dates. E.g, columns A and B contain the following entries: 6/14/2015 6/13/2015 6/12/2015 0.045 6/11/2015 6/10/2015 This means that the stock paid a dividend of 0.045 on 6/12/2015. In another sheet "AdjClose", I have a table with weekly...

Increment count in column based on value in column

excel,google-spreadsheet,excel-formula,array-formulas
I've 2 columns A and B. A contains names and B contains the count of those names. Ex: let A1 have value "fruits". Then B1 must have value 1 because this is first time "fruits" is present in A. Next A2 has "flowers", and B2 will have 1 against "flowers"....

Need to find a value that matches two columns of criteria. Possible VLOOKUP

excel,excel-formula
Update Below -- 6/4 I have two worksheets in excel. One is a list of donors with their check#/amount/ Donor ID ( "donations" worksheet) and the other is a copy of the accounting info with Donor ID/check#/amounts (quickbooks worksheet). Quickbooks does not have the DonorID's filled in yet. The issue...

Converting Date Into a specific text format in excel

excel,excel-formula,excel-2010,excel-2013
Ok so i have been trying to do this and i pretty much keep on failing :p Basically what i want to do is take the date 10/1/2013 and turn it into 201310. I tried to concatenate using left and right. tried to make it into text using left and...

Auto fill in the blanks in Excel [duplicate]

excel,excel-formula
This question is an exact duplicate of: Copy cell from a coloumn if value is starting with #excel #formula [duplicate] 3 answers I have 3 columns of data in Excel spreadsheet, column 1 is Business Unit, column 2 is item_ID and column 3 is Cost. Column 2 and 3...

SUM 3 cells in a range, which are numeric

excel,excel-formula
I am experimenting with the excel functions. I am trying to obtain the sum of a range of cells (A3:F3). However, I only want the first three months that are numeric from the last month. So given the table below. 1| ----- A -------------- B -------------- C------------ D ------------- E...

VBA =COUNTIFS from different file, using variable for R1C1

excel,vba,excel-vba,excel-formula,excel-2010
so I am trying to turn this formula into VBA code: =COUNTIFS('Report'!$J:$J,"=In Force") However, I have acquired the column in the 'Report' file that I will be extracting the data from. The column number it stores in variable ColNum. I need to ensure that it extracts data from the column...

MS Excel, How to make the IF formula spit back the value of the cell if condition is FALSE?

excel-formula
Here is the formula I am trying to run: =IF((FIND("(",A24)-1),LEFT(A24,FIND("(",A24)-1),A24) I thought that the last A24 meant that the cell value would be copied if the IF condition is false, but it is not being copied. How would one make that happen? Thanks! ...

Aging formula without using additional column

excel-formula,countif
I have a table like below ID StarDate EndDate 123 6/5/2015 15:54 6/12/2015 15:54 124 6/6/2015 15:54 6/8/2015 15:54 125 6/7/2015 15:54 6/9/2015 15:54 126 6/8/2015 15:54 6/11/2015 15:54 127 6/9/2015 15:54 6/15/2015 15:54 I want to get the total count of aging without using any additional column 0-30 =COUNTIFS(A:A,">124",A:A,"<127",TODAY()-B2:B6,"<=30")...

COUNTIF with OR Statement

excel,excel-formula,worksheet-function,excel-2013,countif
I'm trying to count the number of times two statements occur. The problem is that if the first statement occurs then it won't check for the second. Maybe if easier if I explain like this: If statement1= true then count = 1 and move next else if statement2= true count...

Horizontal Leader Board based on organisation

excel-formula,lookup,ranking
I am trying to work out the ranking of top 3 users at different organisations and have the data presented horizontally for each user so it can be inputted into our email system to personalise emails. I am able to create a ranking vertically but I am not sure how...

Excel lookup value for multiple criteria and multiple columns

excel,excel-formula
I am helping a friend with some data analysis in Excel. Here's how our data looks like: Car producer | Classification | Prices from 9 different vendors in 9 columns AUDI | C | 100 200 300 400 500 600 700 800 900 AUDI | C | 100 900 800...

Ranking with subsets

excel,excel-formula,rank
I'm trying to rank values and have managed to work out how to sort ties. My data looks at the total number of entries, ranks based on that and if there is a tie it looks to the next column of values to sort them out. However, I have two...

How to set column conditionally in Excel?

excel,excel-formula
I have been working on a spread sheet for work for about a week and a half now and have been stuck in the same place. I am working on something for payroll purpose. So A1 would have an employee name as would the rest of the column. B2 would...

How do I increase from one number to another?

excel-formula
This is a very basic Excel problem. I have a number that I'm starting with, 1000, in cell B2. The number I'd like to increase to is 135,000, located in B40. How would I go about using a formula to increase from 1000 to 135,000 in a way that would...

Excel formula to VBA code

excel,vba,excel-vba,excel-formula
How would I go about turning the excel formula =B4/B5 into vba code? So far, this is my code which does not work of course: ActiveCell.FormulaR1C1 = "=B4/B5" As you can tell, I am not very familiar with R1C1. If needed, I have created a function Col_Letter() which takes a...

How to insert excel formula to cell in Report Builder 3.0

sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

Match pattern of a list of words and highlight the found results

excel,excel-formula,worksheet-function
I want Excel to look up which row in column1 contains those strings in column2. When found, highlight those rows in column1. Then I can group those rows together and use them for something else. I have a big data set so I need this to be automated. Column1 Column2...

Is there a way to sum values to the right of a + symbol across many cells?

excel,excel-formula
I am normally well versed in Excel but I am having a blank on this one. I have a bunch of formulas eg: VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 13, FALSE)+3162 VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 22, FALSE)+18 VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 31, FALSE) VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 40, FALSE)+8.6 and so on What I need is...

Excel Formula - IF & “X” > 0

excel,excel-formula
I have a formula that I'm using in Excel to determine what the payback of a value is after "X" of months. But I'd like to modify the formula to display 0 or $0.00 if the result of the formula is less than $0.00 The current formula is =C2-(C2*(G2/24)) But...

Search a string in multiple columns

excel,excel-formula
I am using the following formula: =IF(ISERROR(LOOKUP(2^15;SEARCH(MID(A1;1;9);$D$1:$D$100)));"No";"Yes") this is working perfectly! Question: I want to search within columns $D$1:$E$100 and not only one column D. How can I modify this to search in two columns?...

Strange behavior in Excel when counting Work Hours and Work Super Hours.

excel,excel-formula
I composed the next Worksheet in order to count my Workhours. Other solutions proposed over the internet would not work, since the Workhours amount per day was not fixed at first. The problem makes its appearrance sometimes when the "Total Work Hours" equal the "Normal Work Hours". When i try...

Lookup value next to the first number in column

excel,excel-formula
I need a formula that can find the date in a cell next to another that has the first number in the column. In column A I have dates and in column B onward I have stock prices. The dates goes back to 1990 daily, but not all the stock...

Changing series formula with VBA. Error Code 1004

excel,vba,charts,excel-formula,series
I'm working on an excel project that helps visualising blood work in generating a chart for each single value. As I'm trying to prevent empty values in charts I added two sheets that actually manage the data ("#data") and charts ("#charts"). In "data" one enters values and dates, in "charts"...

Multiply values based on numerous criteria

excel,if-statement,excel-formula,excel-2007
I've been having a lot of trouble lately with this and haven't been able to find a solution yet. Here's my problem: I have two sheets: http://s4.postimg.org/aryzvl13v/Capture.png < image of the two tables I have to find the "sell price" in the first table. In order to do this I...

Search columns and return number of cells containing “5” against colleague name

excel,excel-formula,excel-2007,formula
What I'm looking for is a formula that will do the following: (I'll be using the first row for this example): Firstly check cells E:J for cells containing a 5. If all cells contain a 5 it will check the person's name and then add this to the box in...

How can I write an Excel query that counts unique text data while also excluding data?

excel,excel-formula
Given the following data in Excel, how can I write a query to: a) Show the number of unique (one time) logins only and b) Exclude "admin" user logins in the count The result should be 3. The data: admin admin admin admin admin admin admin admin admin admin jdoe...

Python not getting the right value in an Excel cell

excel,python-2.7,excel-formula,win32com
I want to color the interior of a cell according to it's content, however when I'm accessing its value I'm always getting '1.0', the value is calculated. Colorisation code : def _colorizeTop10RejetsSheet(self): """Colore les position de la page "Top 10 Rejets" """ start_position = (5, 12) last_line = 47 for...

Excel search and match two cells in the same row for multiple rows

excel,excel-formula
I am attempting to accomplish a look-up in Excel and I can't seem to find anything about it online. Admittedly, I may not be searching for the answer in the right way. So if this is redundant and has already been answered I apologize upfront. I'll attempt to describe my...

Conditionally Format Minimum, Unique Value

excel,excel-formula,conditional-formatting
I'm trying to conditionally format a column so the minimum, unique value is highlighted. So, for instance if the column looked like: 1 1 2 3 2 would be the minimum of the unique values. So, I'm trying to conditionally format the cell containing the number 2 in this example....

Cannot use an Excel function without errors

excel,excel-formula
I have a very large excel function which has worked for me in the past (we have been using this spreadsheet for over a year), but now it is not working. Here is a subsection of the formula =sum(A1,B2,C3,D4,E5,F6,G7... ZX529,ZY827,ZZ912) Why am I having this issue?...

Counting values embedded in strings inside a column (Google Spreadsheets)

excel,google-spreadsheet,excel-formula,formula,countif
I have a Google Survey where I created some multiple choice questions. Now, I am trying to count the responses. [A] [B] [Response#] [Selections] [1] [Apple,Orange,Banana] [2] [Orange,Banana] [3] [Apple,Orange,Banana] [4] [Banana] [5] [Apple,Banana] [6] [Apple,Orange] . So on my summary spreadsheet, I would like to have the totals: [Favorite...

Formula to find value between date ranges and then use the figure next to it

excel,excel-formula
I have developed a worksheet for my office work. I have also developed the formula that will be used in it. However, the formula is quite long and i want to be shortened. Here is the detail for the formula. =IF( AND( [Date]>=DATE(2014,7,1), [Date]<=DATE(2014,7,31), [Description]="Diesel Oil"), [Value Ex. Sales Tax]*'Tax...

Converting letters to text and back again (Excel 2013)

excel,excel-formula,formula,vlookup
For a piece of coursework I have to complete a register of student grades. I am trying to calculate their overall grades by converting their grades from each of the four units into numbers (which I have done using the VLOOKUP function), but I need to then convert the result...

How to count multiple values in one single cell using Pivot Tables in Excel?

excel,excel-formula,pivot-table
I have this table in a Excel sheet: I would like to create a Pivot Table to have this outcome: Do you guys have any idea how to do it? Thank you!...

Excels INDEX MATCH - Finding multiple matches

excel,excel-formula
------A ----------------- B ----------------------C ------------------------D 1 --First--------------Last-----------------Start Date--------------End Date 2 --John--------------Smith--------------08/08/2014------------01/01/2015 3---John--------------Smith--------------08/11/2014------------17/11/2014 4---John--------------Smith--------------06/06/2014------------23/12/2014 5---Abel--------------Jones--------------14/05/2014------------29/04/2015...

Excel - How to concatenate 2 values to make a refference

excel,excel-formula,excel-2013
I have a long column of data (15000 values) that simplified looks like this: A B C D 1 lorem pellen Vestibulum 2 epsum tesque pretium 3 Morbi vel convallis 4 fermentum tellus nibh 5 Interdum molestie Vi .. 15000 Then I have a second table: A B C TYPE...

Counting across rows for ###, then countif down columns for ids matching ###

excel,excel-formula,countif
I'm trying to count the number of activities each organization has done in my dataset. Right now, each row represents a single organization's list of activities. The following formula accurately finds the number of activities per organization: =IF(COUNTA(A1:H1)=5,"yes") However, I now need to group organizations by amount of activities (ex:...

Pulling data out of an worksheet by country

excel,excel-formula,excel-2013
I have a huge amount of people in my excel sheet and I want to split them by country with excel coding, here is an example of my data: Country | Name UK | Tom Austria | Bobsky UK | Ralf Germany | Badolf Germany | Schwartz UK | Andy...

How to create a dynamic table in Excel?

mysql,excel,excel-vba,excel-formula
I am trying to create a dynamic table - I have tried a Pivot Table, but cannot get it to work. So I thought that maybe it could be done with an IF-statement, but that did not work for me neither. Basically, I have 2 tables, 1 table containing the...

Vloopup + INDEX and transpose

excel,excel-formula
I really need help with the following situation . I'm trying to use a vlookup or index that I believe is really advance for me , unfortunately I don't have the knowledge how to attached an excel file here so I will add a image I have a huge data...

Sum the values from the days in a specific week in Excel

excel,excel-formula,week-number
So I have some rows of data and some columns with dates. As you can see on the image below. I want the sum of the week for each row - but the tricky thing is that not every week is 5 days, so there might be weeks with 3...

Conditional formatting if a cell does not equal one of three values [closed]

excel,excel-formula,conditional-formatting
I have a dataset as below: I want to apply conditional formatting that will format a cell if the value in that cell is not 100, 50 or 0 (ie I want it to pick up A5 and format it) I'm new to conditional formatting and haven't been able to...

Horizontal Index Match (returning column header)

excel,excel-formula
A B C 1 Fruit Color Meat <- Column Header 2 Banana Red Pork 3 Apple Black Chicken 4 Orange White Beef From the table1 above to table2 below: A B 1 Name What? <- Column Header 2 Banana Fruit <- Formula should return these values, based on table...

Evaluating INDIRECT in an array in Excel

excel,excel-formula
I'm trying to write a linear regression function that dynamically references columns, can handle #N/A values, and will function as additional rows are added over time. Here is a sample dataset: Date Value 1 Value 2 1/2/1991 #N/A #N/A 2/4/2002 276.36 346.31 1/7/2003 252 350 1/21/2004 232 345.5 1/6/2005 257...

Determine longest date range, from two date ranges - Excel

excel,excel-formula
I have the following excel table ------A ----------------- B ----------------------C ------------------------D 1 --First--------------Last-----------------Start Date--------------End Date 2 --John--------------Smith-------------10/09/2014------------24/11/2014 3---John--------------Smith--------------20/11/2014------------31/01/2015 (Occasionally I have duplicate names on a spread sheet). I am creating a formula which determines the number of days between the earliest start date and latest end date from two date...